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.)
Answer
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:
I would really appreciate it if any expert could help me to do this complex coding.
Thanks
Mirisage
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.