%macro update(before);
proc sql outobs=5;
CREATE TABLE A.Transaction_Data_Before_&before as
SELECT YEAR(Trans1.date2), MONTH(Trans1.date2),Trans1.account_id, SUM(CASE WHEN type = "PRIJEM" THEN Trans1.amount ELSE 0 END) as credit_sum_&before,
SUM(CASE WHEN type = "VYDAJ" THEN Trans1.amount ELSE 0 END) as withdrawal_sum_&before,
coalesce(sum(type = "PRIJEM"),0) as credit_count_&before,
coalesce(sum(type = "VYDAJ"),0) as withdrawal_count_&before,
(SELECT
MAX(Trans2.balance)
FROM A.TRANS as Trans2
WHERE
Trans2.account_id = Trans1.account_id
AND YEAR(Trans2.date2) = YEAR(INTNX('month',Loan.date2,-&before))
AND MONTH(Trans2.date2) = MONTH(INTNX('month',Loan.date2,-&before))) as balance_end_of_month_&before
FROM A.LOAN as Loan
LEFT JOIN A.Trans as Trans1 ON Loan.account_id = Trans1.account_id
WHERE MONTH(Trans1.date2) = MONTH(INTNX('month',Loan.date2,-&before))
AND YEAR(Trans1.date2) = YEAR(INTNX('month',Loan.date2,-&before))
GROUP BY YEAR(Trans1.date2), MONTH(Trans1.date2), Loan.account_id;
%mend;

%update(1);