## How to track how many dollars and # of accounts moved from cycle-to-cycle over one month to another?

Super Contributor
Posts: 338

# How to track how many dollars and # of accounts moved from cycle-to-cycle over one month to another?

Hi SAS Forum,

I have 6 accounts (SAS data set is attached). For clarity I reproduced the dataset below too.

 Bank_number Account_number Current_date Product Balance Arrears_Band 10 1111111 28-Feb-10 Personal Loan 12 Current 10 1111111 31-Mar-10 Personal Loan 555 30 - 60 10 1111111 30-Apr-10 Personal Loan 200 Current 10 1111111 31-May-10 Personal Loan 108 1 - 30 30 2222222222 31-Dec-10 Res. Mortgage 600 Current 30 2222222222 31-Jan-11 Res. Mortgage 600 90 + 30 2222222222 28-Feb-11 Res. Mortgage 600 NPNA 30 2222222222 17-Mar-11 Res. Mortgage 600 writoff 20 333333333 1-Feb-10 Personal OD 625 NPNA 20 333333333 15-Mar-10 Personal OD 700 NPNA 20 333333333 30-Apr-10 Personal OD 3 NPNA 20 333333333 31-May-10 Personal OD 2 NPNA 20 333333333 30-Jun-10 Personal OD 1 NPNA 10 444444444 28-Feb-10 Personal Loan 8 1 - 30 10 444444444 31-Mar-10 Personal Loan 800 60 - 90 10 444444444 30-Apr-10 Personal Loan 2 NPNA 10 444444444 31-May-10 Personal Loan 25 NPNA 10 444444444 30-Jun-10 Personal Loan 32 NPNA 70 666666666 7-Feb-10 Personal OD 4 Current 70 666666666 15-Mar-10 Personal OD 666 Current 40 777777 8-Feb-10 Res. Mortgage 877 Current 40 777777 30-Apr-10 Res. Mortgage 905 Current

Question:

(i). I need to determine the dollar amount that would move from a certain delinquency bucket in preceding month (Feb 2010) to succeeding month (March 2010).

(ii). And the corresponding number of accounts.

(In fact, I have to repeat the calculation from MAR2010 to APR2010, APR2010 to MAY2010 and so on which I can do when I learn the first code from the forum.)

I should get the below two dimensional 2 tables, A and B.

In these two tables, left hand side blue colored column shows the buckets from which dollars (or accounts) move from the preceding month (Feb 2010).

The blue colored row shows which buckets the \$ s or accounts moved into in the succeeding month (March 2010).

 Table A: \$ s Moving from Bucket to Bucket from FEB 2010 to MAR 2010 Variable = Balance (\$) Succeeding Month (March 2010) Current 1 - 30 30 - 60 60 - 90 90 + NPNA writoff Current 905 555 1 - 30 800 Preceeding Month 30 - 60 (Feb 2010) 60 - 90 90 + NPNA 700 writoff Table B: # of Accounts Moving from Bucket to Bucket from FEB 2010 to MAR 2010 # of Accounts Succeeding Month (March 2010) Current 1 - 30 30 - 60 60 - 90 90 + NPNA writoff Current 1 1 1 - 30 1 Preceeding Month 30 - 60 (Feb 2010) 60 - 90 90 + NPNA 1 writoff

Example:

• Account_number 1111111 was in “Current” bucket in FEB2010. It moved into “30 – 60” bucket in MAR2010 when the “Balance” was \$ 555. So, in Table A above, the yellow color grid should get 555. In Table B, the corresponding grid should get value 1 to reflect that account 1111111 ‘s cycling.

• We do not take any value from account_number 2222222222 because this account doesn’t cycle from FEB2010 to MAR2010.

• Account_number 333333333 was in “NPNA” bucket in FEB2010. It moved into “NPNA” bucket in MAR2010 when the “Balance” was \$ 700. So, in Table A above, the red color grid should get 700. In Table B, the corresponding grid should get value 1 to reflect that account 333333333 ‘s cycling.

• Account_number 444444444 was in “1 - 30” bucket in FEB2010. It moved into “60 - 90” bucket in MAR2010 when the “Balance” was \$ 800. So, in Table A above, the green color grid should get 800. In Table B, the corresponding grid should get value 1 to reflect that account 444444444 ‘s cycling.

I would really appreciate it if any expert could help me to do this complex coding.

Thanks

Mirisage

Discussion stats
• 0 replies
• 145 views
• 0 likes
• 1 in conversation