Hi
I've trying to produce a summary of reducing balances. When a withdrawal is made, it is applied proportionally to to the previous deposits. For example, On the 17th March, a withdrawal of 30 is made. The 30 reduces the deposits made on 1st jan, 2nd Feb and 5th Mar by the proportion they account for (73%,9%,18%), resulting in 58,7,15 for these depoists as at 17th March. All OK so for....until a new withdrawal is made. The proportions need to be based on the previously reduced balances and any new deposits. This works. However, the previously reduced balances when output for a third time on 6th June show the original balances. Any suggestions on how to carry the reduced balances forward??
data temp;
format dep_date wth_date date9.;
input dep_date : date9.
customer_number : $3.
trans_id : $4.
deposit : 8.
wth_date : date9.
withdraw : 8.
;
datalines;
'01jan2014'd ID1 CID1 80 '17mar2014'd 30
'02feb2014'd ID1 CID2 10 '17mar2014'd 30
'05mar2014'd ID1 CID3 20 '17mar2014'd 30
'22apr2014'd ID1 CID4 10 '06jun2014'd 20
'20may2014'd ID1 CID5 40 '06jun2014'd 20
;
run;
data test ;*(drop=rci rcd);
format balance_date date9.;
set temp;
by customer_number wth_date;
balance=deposit;
balance_date=dep_date;
keyval=_n_;
if first.customer_number then do;
tot=0;
declare hash cust(ordered: 'yes');
declare hiter citer('cust');
cust.defineKey('keyval');
cust.defineData('trans_id','deposit','balance','balance_date');
cust.defineDone();
end;
if first.wth_date then do;
declare hash h(ordered: 'yes');
declare hiter iter('h');
h.defineKey('keyval');
h.defineData('trans_id','deposit','balance');
h.defineDone();
end;
tot+deposit;
cust.add();
h.add(); /* add rows to the hash table */
output;
if last.wth_date then do;
byct=h.num_items;
do i=1 to byct;
if i=1 then rcf=iter.first();
else rcn=iter.next();
prop=deposit/tot;
balance=int(deposit-(prop*withdraw));
balance_date=wth_date;
if byct=i then tot=tot-withdraw;
output;
end;
rci=iter.delete();
rcd=h.delete();
end;
if first.wth_date then do;
byct2=cust.num_items;
do x=2 to byct2;
if x=1 then rcf2=citer.first();
else rcn2=citer.next();
prop=deposit/tot;
balance_date=wth_date;
balance=balance*prop;
output;
end;
end;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.