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;
... View more