data have;
length ln Status $25. ;
input ln Status Month date9. Curr Due_030;
return;
datalines;
122 Current 01Nov2019 1 0
122 Current 30Nov2019 1 0
122 Current 01Dec2019 1 0
122 Due_030 31Dec2019 0 1
;run;
data have2;
set have;
format month date9.;
run;
Produces this output
ln | Status | Month | Curr | Due_030 |
122 | Current | 01Nov2019 | 1 | 0 |
122 | Current | 30Nov2019 | 1 | 0 |
122 | Current | 01Dec2019 | 1 | 0 |
122 | Due_030 | 31Dec2019 | 0 | 1 |
proc transpose data=have2 out=have3 let;
id month;
by Status;
;run;
Produces this output from transpose
Status | _NAME_ | 01NOV2019 | 30NOV2019 | 01DEC2019 | 31DEC2019 |
Current | Curr | 1 | 1 | 1 | |
Current | Due_030 | 0 | 0 | 0 | |
Due_030 | Curr | 0 | |||
Due_030 | Due_030 | 1 |
My issue is I need to be able to compare the beginning status with the ending status by month and produce stats
As an example for have 2 ln 122 began as current for Dec1 then became delinquent by Dec31. Therefore the percentage roll from current to delinquent would be 100%. I thought using proc transpose would allow me to analyze horizontal instead of vertical however look what transpose does to the Due_30. Is there a way to do the analysis in a "roll" fashion while looking at changes in status for the 1 month time period as shown here. I am using this as an example. I also enclose a mockup of the actual report I need to re-create
I have looked at your 2-sheet workbook, and you have not explained anywhere near what I would need to understand before offering a strategy. Please don't ask us to read your mind - I am typically not good at it. I suppose I can guess (but why ask me to guess?) the meaning of COUNT (number of observations) and UPB (unpaid balance?). But "stayed", "rolled worse", "rolled current", "stayed 30-59" (in sheet 1), rolled current, rolled 30-59, etc. (sheet2)? I am not going to make guesses that could easily be wrong.
Please provide the definition (and logical algorithm - in words if you prefer) for the terms in your report.
Help us help you.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.