Help using Base SAS procedures

How to omitt “non 90+” balances from summing up for NPNA in a longitudinal dataset

Reply
Super Contributor
Posts: 338

How to omitt “non 90+” balances from summing up for NPNA in a longitudinal dataset

Hi Community,

The code below (thankfully developed through "Alpey") sums the “balance” across “current_date” within each “account_number” subject to a number of conditions necessitated by the needs of business I am trying to address.

When I apply this code to the attached data set (named “delete_this.sas7bdat"), it generates the resutls (attached “want.xls”).

However, I need to add the following business logic too into the following code while retaining all the existing conditions in it.

New Condition

For a given "account_number", "Balance" in NPNA "Arrears_Band" is summed up across records of that "account_number" only if the value of the "Arrears_Band" in the row immediately preceding to NPNA row is “90 +”.

In other words, for a given "account_number", only those balances that have fallen from “90 +” Arrears_Band to NPNA is summed up as the NPNA balance.

Based on this condition, the answer should exclude $4 and $3 respectively in 30SEP2010 and 31OCT2011(see attached “want.xls”).

Could Alpey or any one help me?

  • p.s. Account number 66 has started as well as ended in NPNA status, so its balance is not summed up by the existing code which is correct.

Thank you

Mirisage

data temp;

    if _n_ = 1 then do;

        declare hash h(ordered:'a');

        h.defineKey('Current_Date');

        h.defineData('Current_Date','Bank_Number','Account_Number','Balance','Product','Arrears_Band','NPNA_Start_Month');

        h.defineDone();

        declare hiter hi('h');

    end;

    NPNA_Start_Month = .;

    do until(last.Account_Number);

        set delete_this;

        where  Product IN ('Personal OD','Personal Loan','Res. Mortgage') and

        Bank_number = 10;

        by Account_Number;

        if Arrears_Band = 'NPNA' and NPNA_Start_Month = . then NPNA_Start_Month = Current_Date;

        else if Arrears_Band ne 'NPNA' then NPNA_Start_Month = .;

        h.replace();

    end;

    Last_NPNA_Start_Month = NPNA_Start_Month; /* Added 6/18/2012 */

    rc = hi.first();

    do while(rc=0);

        Current = 0;

        One_to_30 = 0;

        Thirty_to_60 = 0;

        Sixty_to_90 = 0;

        Ninety_plus = 0;

        NPNA = .;

        if NPNA_Start_Month = . or (

                NPNA_Start_Month = Current_Date and /* NPNA_Start_Month > '28Feb2010'd */

                Last_NPNA_Start_Month ne '28Feb2010'd) then do;

            select (Arrears_Band);

                when ('Current') do;Current = Balance;end;

                when ('1 - 30') do;One_to_30 = Balance;end;

                when ('30 - 60') do;Thirty_to_60 = Balance;end;

                when ('60 - 90') do;Sixty_to_90 = Balance;end;

                when ('90 +') do;Ninety_plus = Balance;end;

                when ('NPNA') do;NPNA = Balance;end;

            end;

        end;

        output;

        rc = hi.next();

    end;

    h.clear();

run;

proc summary data=temp nway missing;

    class Current_Date;

    var Current One_to_30 Thirty_to_60 Sixty_to_90 Ninety_plus NPNA;

    output out=want(drop=_Smiley Happy

    sum=;

run;

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