Hi SAS Forum,
This SAS data set shows how 3 bank accounts in different banks are swinging from one arrears_band to the other from month to month (months are not consecutive sometimes).
data one;
informat Current_date date9.;
input Bank_number Current_date Account_number $ 14 -17 Balance Product $ 24 -36 Arrears_Band $ 38-44;
format Current_date date9.;
cards;
10 31Jul2011 4444 1000 Res. Mortgage Current
10 31Aug2011 4444 1200 Res. Mortgage 1 - 30
10 30Sep2011 4444 1100 Res. Mortgage 30 - 60
10 31Oct2011 4444 1400 Res. Mortgage 60 - 90
10 30Nov2011 4444 1000 Res. Mortgage 90 +
30 28Feb2010 1111 3420 Res. Mortgage NPNA
30 31Mar2010 1111 3308 Res. Mortgage NPNA
30 30Jun2011 1111 2614 Res. Mortgage NPNA
30 31Jul2011 1111 2599 Res. Mortgage 1 - 30
30 31Aug2011 1111 2599 Res. Mortgage Current
30 30Sep2011 1111 2599 Res. Mortgage 60 - 90
30 31Oct2011 1111 2599 Res. Mortgage NPNA
30 30Nov2011 1111 2591 Res. Mortgage NPNA
30 31Dec2011 1111 2584 Res. Mortgage 90 +
30 31Jan2012 1111 2216 Res. Mortgage writoff
30 28Feb2012 1111 1492 Res. Mortgage NPNA
30 31Mar2012 1111 1487 Res. Mortgage NPNA
30 31Aug2012 1111 1425 Res. Mortgage 90 +
40 28Feb2010 1111 1492 Res. Mortgage NPNA
40 31Mar2010 1111 1487 Res. Mortgage NPNA
40 31Aug2010 1111 1425 Res. Mortgage NPNA
40 30Sep2010 1111 7226 Res. Mortgage writoff
40 28Feb2011 1111 3510 Res. Mortgage NPNA
40 31Mar2011 1111 3510 Res. Mortgage 90 +
;
run;
I have to return the sum of variable “Balance’ for each “Arrears_band”, grouped by “Current_date” subject to certain business rules.
RULES
For clarity, I HAVE ANNOTATED “balances” of what records should be taken/not taken (W.O. means writeoff)
data one;
informat Current_date date9.;
input Bank_number Current_date Account_number $ 14 -17 Balance Product $ 24 -36 Arrears_Band $ 38-44;
format Current_date date9.;
cards;
10 31Jul2011 4444 1000 Res. Mortgage Current /*take*/
10 31Aug2011 4444 1200 Res. Mortgage 1 – 30 /*take*/
10 30Sep2011 4444 1100 Res. Mortgage 30 – 60 /*take*/
10 31Oct2011 4444 1400 Res. Mortgage 60 – 90 /*take */
10 30Nov2011 4444 1000 Res. Mortgage 90 + /*take*/
30 28Feb2010 1111 3420 Res. Mortgage NPNA /*take leading NPNA */
30 31Mar2010 1111 3308 Res. Mortgage NPNA /*don’t take */
30 30Jun2011 1111 2614 Res. Mortgage NPNA /*don’t take */
30 31Jul2011 1111 2599 Res. Mortgage 1 – 30 /*take */
30 31Aug2011 1111 2599 Res. Mortgage Current /*take */
30 30Sep2011 1111 2599 Res. Mortgage 60 – 90 /*take */
30 31Oct2011 1111 2599 Res. Mortgage NPNA /*take leading NPNA */
30 30Nov2011 1111 2591 Res. Mortgage NPNA /*don’t take */
30 31Dec2011 1111 2584 Res. Mortgage 90 + /*take*/
30 31Jan2012 1111 2216 Res. Mortgage writoff /*take*/
30 28Feb2012 1111 1492 Res. Mortgage NPNA /*after W.O, don’t take */
30 31Mar2012 1111 1487 Res. Mortgage NPNA /* after W.O. don’t take*/
30 31Aug2012 1111 1425 Res. Mortgage 90 + /* after W.O. don’t take*/
40 28Feb2010 1111 1492 Res. Mortgage NPNA /*take leading NPNA*/
40 31Mar2010 1111 1487 Res. Mortgage NPNA /*don’t take */
40 31Aug2010 1111 1425 Res. Mortgage NPNA /*don’t take */
40 30Sep2010 1111 7226 Res. Mortgage writoff /*take*/
40 28Feb2011 1111 3510 Res. Mortgage NPNA /* after W.O. don’t take */
40 31Mar2011 1111 3510 Res. Mortgage 90 + /* after W.O. don’t take */
;
run;
When we do the above transposing correctly, we should get the table 1 below.
Table 1: This is the table I want
date | Current | One_to_30 | Thirty_to_60 | Sixty_to_90 | Ninety_plus | NPNA | writoff |
28Feb2010 | 0 | 0 | 0 | 0 | 0 | 1,492 + 3,420 | 0 |
31Mar2010 | 0 | 0 | 0 | 0 | 0 | 3,308 | 0 |
31Aug2010 | 0 | 0 | 0 | 0 | 0 | . | 0 |
30Sep2010 | 0 | 0 | 0 | 0 | 0 | . | 7,226 |
28Feb2011 | 0 | 0 | 0 | 0 | 0 | 3,510 | 0 |
31Mar2011 | 0 | 0 | 0 | 0 | 3,510 | . | 0 |
30Jun2011 | 0 | 0 | 0 | 0 | 0 | . | 0 |
31Jul2011 | 1,000 | 2,599 | 0 | 0 | 0 | . | 0 |
31Aug2011 | 2,599 | 1,200 | 0 | 0 | 0 | . | 0 |
30Sep2011 | 0 | 0 | 1,100 | 2,599 | 0 | . | 0 |
31Oct2011 | 0 | 0 | 0 | 1,400 | 0 | 2,599 | 0 |
30Nov2011 | 0 | 0 | 0 | 0 | 1,000 | . | 0 |
31Dec2011 | 0 | 0 | 0 | 0 | 2,584 | . | 0 |
31Jan2012 | 0 | 0 | 0 | 0 | 0 | . | 2,216 |
29Feb2012 | 0 | 0 | 0 | 0 | 0 | 1,492 | 0 |
31Mar2012 | 0 | 0 | 0 | 0 | 0 | . | 0 |
31Aug2012 | 0 | 0 | 0 | 0 | 1,425 | . | 0 |
Question:
The code below (Soren is gratefully acknowledged) produces the output in Table 2 which is slightly different from the Table 1 output which I want.
I wonder if any one of you could help me to tweak this code to get table 1.
Table 2: This is the output generated by our existing code
date | Current | One_to_30 | Thirty_to_60 | Sixty_to_90 | Ninety_plus | NPNA | writoff |
28Feb2010 | 0 | 0 | 0 | 0 | 0 | 1,492 | 0 |
31Mar2010 | 0 | 0 | 0 | 0 | 0 | 3,308 | 0 |
31Aug2010 | 0 | 0 | 0 | 0 | 0 | . | 0 |
30Sep2010 | 0 | 0 | 0 | 0 | 0 | . | 7,226 |
28Feb2011 | 0 | 0 | 0 | 0 | 0 | 3,510 | 0 |
31Mar2011 | 0 | 0 | 0 | 0 | 3,510 | . | 0 |
30Jun2011 | 0 | 0 | 0 | 0 | 0 | . | 0 |
31Jul2011 | 1,000 | 2,599 | 0 | 0 | 0 | . | 0 |
31Aug2011 | 2,599 | 1,200 | 0 | 0 | 0 | . | 0 |
30Sep2011 | 0 | 0 | 1,100 | 2,599 | 0 | . | 0 |
31Oct2011 | 0 | 0 | 0 | 1,400 | 0 | 2,599 | 0 |
30Nov2011 | 0 | 0 | 0 | 0 | 1,000 | . | 0 |
31Dec2011 | 0 | 0 | 0 | 0 | 2,584 | . | 0 |
31Jan2012 | 0 | 0 | 0 | 0 | 0 | . | 2,216 |
29Feb2012 | 0 | 0 | 0 | 0 | 0 | 1,492 | 0 |
31Mar2012 | 0 | 0 | 0 | 0 | 0 | . | 0 |
31Aug2012 | 0 | 0 | 0 | 0 | 1,425 | . | 0 |
/*THIS IS THE EXISTING CODE*/
/*sorting by 3 variables that make a unique record*/
proc sort data=one out=TEMP_1;
by bank_number account_number current_date;
run;
/* find the last date where the account changes to NPNA */
data last_start_npna;
do until(last.account_number);
set TEMP_1;
by bank_number account_number arrears_band notsorted;
if first.Arrears_band and Arrears_band='NPNA'
then Last_NPNA_Start_Month = Current_Date;
end;
keep bank_number account_number Last_NPNA_Start_month;
run;
data temp;
merge TEMP_1 last_start_npna;
by bank_number account_number;
if Arrears_Band = 'NPNA' and (NPNA_Start_Month = . or
NPNA_Start_Month = Current_Date;
else if Arrears_Band ne 'NPNA' then NPNA_Start_Month = .;
retain NPNA_Start_Month;
Current = 0;
One_to_30 = 0;
Thirty_to_60 = 0;
Sixty_to_90 = 0;
Ninety_plus = 0;
writoff = 0;
if NPNA_Start_Month = . or (
NPNA_Start_Month = Current_Date and
Last_NPNA_Start_Month ne '28Feb2010'd) then
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;
when ('writoff') do;writoff = Balance;end;
otherwise put Arrears_Band=; /*added sasel suggestion to avoid error messege, then worked*/
end;
run;
/*summarizing*/
proc summary data=temp nway missing;
class Current_Date;
var Current One_to_30 Thirty_to_60 Sixty_to_90 Ninety_plus NPNA writoff;
output out=want(drop=_:)
sum=;
run;
proc print data=want; run;
Thank you very much for your time and support.
Mirisage
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.