Does it have to be SQL code? Using hash object along with a hash iterator in a data step can be an alternative way to address this issue.
Read records by Account_Number and check out whether Arrears_Band = 'NPNA' or not.
If so and the value of NPNA_Start_Month is missing then set it equal to Current_Date. Else, set NPNA_Start_Month value to missing.
Load the record into a hash object. After the last record for given account_number read traverse the records for it using hash iterator object.
Inıtıalize your sum varaiblles
Current = 0;
One_to_30 = 0;
Thirty_to_60 = 0;
Sixty_to_90 = 0;
Ninety_plus = 0;
NPNA = .;
Update the value of your sum variables based on the value of Arrears_Band and output the record. After all records read clear the hash object and start over for the next account_number.
After all records for all account_numbers have been read summarize your temp data set.
data b;
informat Current_date date9.;
Input Bank_number 1-2 Current_date Account_number $14-23 Balance 25-
26 product $28-40 Arrears_Band $42-48 ;
format Current_date date9.;
cards;
10 28FEB2010 1111111111 45 Personal Loan NPNA
10 31MAR2010 1111111111 45 Personal Loan NPNA
10 30APR2010 1111111111 45 Personal Loan NPNA
10 31MAY2010 1111111111 45 Personal Loan NPNA
10 31MAR2010 2222222222 25 Personal Loan Current
10 30APR2010 2222222222 25 Personal Loan Current
10 31MAY2010 2222222222 25 Personal Loan Current
10 28FEB2010 3333333333 24 Personal Loan Current
10 31MAR2010 3333333333 23 Personal Loan 1 - 30
10 30APR2010 3333333333 23 Personal Loan 30 - 60
10 31MAY2010 3333333333 23 Personal Loan 60 - 90
10 30JUN2010 3333333333 23 Personal Loan 90 +
10 31JUL2010 3333333333 85 Personal Loan NPNA
10 30NOV2010 4444444444 25 Personal Loan Current
10 31DEC2010 4444444444 25 Personal Loan NPNA
10 31JAN2011 4444444444 18 Personal Loan 1 - 30
10 28FEB2011 4444444444 18 Personal Loan NPNA
10 31MAR2011 4444444444 18 Personal Loan NPNA
10 30APR2011 4444444444 18 Personal Loan NPNA
10 31MAY2011 4444444444 85 Personal Loan Current
10 31JAN2011 5555555555 25 Personal Loan Current
10 28FEB2011 5555555555 25 Personal Loan NPNA
10 31MAR2011 5555555555 22 Personal Loan NPNA
10 30APR2011 5555555555 21 Personal Loan NPNA
;
run;
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 b;
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;
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) 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=_:)
sum=;
run;
