Help to tweak an existing transpose code to reflect some business rules

[Mirisage]

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

1. 1. From consecutive blocks of NPNA occurrences, only the lead NPNA’s “Balance” should be transposed and summed up for the corresponding arrears_band and current_date

1. 2. When an account has ever hit “writoff” arrears_band, all the “Balances” of whatsoever the arrears_bands that occur after “writoff” arrears_band should be ignored from summing up (Hitting “writoff” marks the end of the account life)

1. 3. We should return the sum of variable “Balance’ for all the other “Arrears_band” situations, grouped by “Current_date” without any restrictions/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

1. first.account_number) then

    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