topic Re: Consecutive records problem? in SAS Procedures

Consecutive records problem?

Mirisage — Tue, 25 Sep 2012 17:34:54 GMT

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

Re: Consecutive records problem?

art297 — Tue, 25 Sep 2012 17:50:26 GMT

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

Re: Consecutive records problem?

Mirisage — Tue, 25 Sep 2012 18:16:35 GMT

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