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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.