DATA Step, Macro, Functions and more

Load hash tables into hash tables?

Reply
Occasional Contributor
Posts: 13

Load hash tables into hash tables?

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;

Ask a Question
Discussion stats
  • 0 replies
  • 174 views
  • 0 likes
  • 1 in conversation