BookmarkSubscribeRSS Feed
Mirisage
Obsidian | Level 7

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

2 REPLIES 2
art297
Opal | Level 21

Why are you trying to combine the information from two different people in two different banks?

Mirisage
Obsidian | Level 7

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

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 647 views
  • 0 likes
  • 2 in conversation