Hi SAS Forum,
This data set is having 2 accounts in two different banks (identical account numbers but entirely different 2 people).
data have;
informat Current_date date9.;
input Bank_number Account_number $ 4-10 Current_date Product $ 22-35
Balance Arrears_Band $ 40-46;
format Current_date date9.;
cards;
10 1111111 28FEB2010 Personal Loan 120 Current
10 1111111 15MAR2010 Personal Loan 555 30 - 60
10 1111111 28APR2010 Personal Loan 200 Current
10 1111111 19MAY2010 Personal Loan 108 1 - 30
40 1111111 28FEB2010 Res. Mortgage 222 Current
40 1111111 22MAR2010 Res. Mortgage 111 30 - 60
40 1111111 11APR2010 Res. Mortgage 333 60 - 90
40 1111111 25MAY2010 Res. Mortgage 444 NPNA
;
run;
Question:
I need to track the dollar "Balance" succession between consecutive months categorized by corresponding "arrears_band" succession.
Answer:
(not necessarily a table like this but any result to that effet).
Succession between which consecutive 2 months | Arrears_Band succession | Balance ($) |
|
|
|
FEB2010 to MAR2010 | Current to 30-60 | 666 (i.e.555 + 111) |
|
|
|
MAR2010 to APR2010 | 30-60 to Current (see bank 10 account 1111111) | 200
|
| 30-60 to 60-90 (see bank 40 account 1111111) | 333 |
|
|
|
APR2010 to MAY2010 | Current to 1-30 (see bank 10 account 1111111) | 108 |
| 60-90 to NPNA (see bank 40 account 1111111) | 444 |
My effort:
Three variables that would make a unique record are Bank_number
Account_number Current_date.
So, I have sorted the data set by them.
Proc sort data=have out=have_sorted;
by Bank_number Account_number Current_date;
run;
Then applied the below code reading from the literature.
DATA have2;
SET have;
BY Bank_number Account_number Current_date;
RETAIN R_balance ;
R_balance = LAG(balance);
IF NOT FIRST.Account_number THEN DO;
DIFF_balance = balance - R_balance;
END;
DROP R_: ;
RUN;
But my current skills are not enough to move forward to get into what I wanted as shown at the top.
Could anyone help me to do the code.
Thank you
Mirisage
Why are you trying to combine the information from two different people in two different banks?
Hi Art,
Actually i finally need to predict the loan loss of the entire lending protfolio. So the O/S balances of individual accounts have to be summed up to get an aggregate dollar figure so that I can then use a roll rate model to do the prediction.
I have to split the entire portfolio into this type of 23 Markov matrices (shown below is just for the FEB to MAR cycle). This coding help supports my entire process down the road.
Table : $ 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 |
|
| $ 666 |
|
|
|
|
| 1 - 30 |
|
|
|
|
|
|
|
Preceeding Month | 30 - 60 |
|
|
|
|
|
|
|
(Feb 2010) | 60 - 90 |
|
|
|
|
|
|
|
| 90 + |
|
|
|
|
|
|
|
| NPNA |
|
|
|
|
|
|
|
| writoff |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Thanks
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.