07-03-2012 01:24 PM
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.
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?
if _n_ = 1 then do;
declare hash h(ordered:'a');
declare hiter hi('h');
NPNA_Start_Month = .;
where Product IN ('Personal OD','Personal Loan','Res. Mortgage') and
Bank_number = 10;
if Arrears_Band = 'NPNA' and NPNA_Start_Month = . then NPNA_Start_Month = Current_Date;
else if Arrears_Band ne 'NPNA' then NPNA_Start_Month = .;
Last_NPNA_Start_Month = NPNA_Start_Month; /* Added 6/18/2012 */
rc = hi.first();
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;
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;
rc = hi.next();
proc summary data=temp nway missing;
var Current One_to_30 Thirty_to_60 Sixty_to_90 Ninety_plus NPNA;