topic Re: Need to create a transition matrix for delinquency buckets in SAS EG in SAS Enterprise Guide

Need to create a transition matrix for delinquency buckets in SAS EG

Satyakshma — Wed, 02 Dec 2020 04:27:30 GMT

Hi,

I have a dataset with variable names M1 - M48 ( where M specifies month) and have DPD Buckets (0-DPD, 1-30 DPD, 31-60 DPD, 61-90 DPD and 91+ DPD) as values. I want to create a transition matrix for these DPD BUCKETS comparing Year-On-Year movement of DPD Buckets. This needs to be done for every M: variable

For eg: Create transition Matrix for M1 and M13, M2 and M14, M3 and M15 and so on. The output expected is:

M1\M13	0 DPD	1 - 30 DPD	31 - 60 DPD	61 - 90 DPD	91+ DPD	Closed
0 DPD	123	0	3	1	5	57
1 - 30 DPD	0	0	0	0	0	0
31 - 60 DPD	2	0	0	0	2	4
61 - 90 DPD	1	0	0	0	2	0
91+ DPD	1	0	0	1	58	5

If I do this using Proc FREQ then how can I create transition matrix on the above mentioned incremental data. Or is there any better way to achieve this output.

Please Suggest!

Re: Need to create a transition matrix for delinquency buckets in SAS EG

mkeintz — Wed, 02 Dec 2020 04:35:05 GMT

Where did "closed" come from? You made no mention of it.

Do you want this in the form of a report? Do you want actual counts, or do you want row percentages (after all this is a "transition" matrix - you may want to look at proportion of each row (M1 value) ending up in a given column (M13 value).

Or, do you want this in the form of a dataset?

Please provide, in the form of a data step, a sample of your data (say just M1, M2, M3, M13, M14, and M14).

Re: Need to create a transition matrix for delinquency buckets in SAS EG

Satyakshma — Wed, 02 Dec 2020 05:56:34 GMT

HI,
Closed accounts are to be created by applying condition - ( if M1 = "0-DPD" AND M13=" " THEN IT IS A CLOSED ACCOUNT) - THIS is to be checked for the M13, M14, M15 AND SO ON

I want the both the counts and row percentages of each row(m1 value) ending up in a given column( m13 value).

/*Sample data*/
DATA TEST;
INFILE DATALINES DELIMITER=',';
INPUT CUSTOMER_NO M1$ M2$ M3$ M4$ M13$ M14$ M15$ M16$ ;
DATALINES ;
1,0-DPD,0-DPD,1-30DPD,0-DPD, ,1-30DPD,31-60DPD,31-6-DPD
2,1-30DPD,0-DPD,1-30DPD,0-DPD,31-60DPD,1-30DPD,31-60DPD,31-6-DPD
3,0-DPD,0-DPD,1-30DPD,0-DPD,1-30DPD,1-30DPD,31-60DPD,31-6-DPD
4,31-60DPD,0-DPD,1-30DPD,0-DPD,0-DPD,1-30DPD,31-60DPD,31-6-DPD
5,91+DPD,61-90DPD,31-60DPD,1-30DPD,31-60DPD,1-30DPD,31-60DPD,31-6-DPD
;
RUN;