- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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).
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set
Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets
--------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;