Help using Base SAS procedures

How to track how many dollars and # of accounts moved from cycle-to-cycle over one month to another?

Reply
Super Contributor
Posts: 338

How to track how many dollars and # of accounts moved from cycle-to-cycle over one month to another?

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:

  • Account_number 1111111 was in “Current” bucket in FEB2010. It moved into “30 – 60” bucket in MAR2010 when the “Balance” was $ 555. So, in Table A above, the yellow color grid should get 555. In Table B, the corresponding grid should get value 1 to reflect that account 1111111 ‘s cycling.

   

  • We do not take any value from account_number 2222222222 because this account doesn’t cycle from FEB2010 to MAR2010.

  • Account_number 333333333 was in “NPNA” bucket in FEB2010. It moved into “NPNA” bucket in MAR2010 when the “Balance” was $ 700. So, in Table A above, the red color grid should get 700. In Table B, the corresponding grid should get value 1 to reflect that account 333333333 ‘s cycling.

   

  • Account_number 444444444 was in “1 - 30” bucket in FEB2010. It moved into “60 - 90” bucket in MAR2010 when the “Balance” was $ 800. So, in Table A above, the green color grid should get 800. In Table B, the corresponding grid should get value 1 to reflect that account 444444444 ‘s cycling.

I would really appreciate it if any expert could help me to do this complex coding.

Thanks

Mirisage

Attachment
Ask a Question
Discussion stats
  • 0 replies
  • 130 views
  • 0 likes
  • 1 in conversation