There is probably a better way of doing this, but here is the initial code that I came up with. In the final "projected balance" dataset, shouldn't the balance on day1 be $25 ($5 inital plus $20 on the first day)?
data account_start;
input account_no $ balance;
datalines;
Account1 0.00
Account2 10.00
Account3 5.00
;
data Transactions;
length AccountFrom $ 13;
input AccountFrom $ Amount DayOfTransaction $ AccountTo $;
datalines;
Account_from1 1.00 day3 Account1
Account_from2 2.00 day5 Account1
Account_from3 20.00 day1 Account3
Account_from4 10.00 day3 Account3
Account_from5 20.00 day5 Account3
;
run;
data account_initial;
set account_start;
array day{5};
do I = 1 to 5;
day{I}=balance;
end;
drop balance i;
run;
proc sort data=transactions;
by AccountTo;
run;
data transactions;
set transactions;
by AccountTo;
array DayTransaction{5};
if first.AccountTo then do;
Do I = 1 to 5;
DayTransaction{I} = 0;
end;
end;
TransactionDay=input(substr(DayOfTransaction, 4, 1),8.);
Do I = TransactionDay to 5;
if I ge TransactionDay then DayTransaction{I} + amount;
end;
if last.AccountTo then output;
keep AccountTo DayTransaction1-DayTransaction5;
run;
proc sort data=account_initial;
by account_no;
data account_final;
merge account_initial transactions(rename=(accountTo = Account_no));
by account_no;
array transaction{5} DayTransaction1-DayTransaction5;
array day{5} Day1-Day5;
do I = 1 to 5;
day{I}=sum(day{I}, transaction{I});
end;
drop daytransaction1-daytransaction5 i;
run;
proc print data=account_final noobs;
format day1-day5 8.2;
run;
... View more
