Roll analysis(vertical or horizontal) for report

Q1983 — Fri, 06 Mar 2020 16:19:30 GMT

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

Re: Roll analysis(vertical or horizontal) for report

mkeintz — Fri, 06 Mar 2020 18:20:13 GMT

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.

topic Re: Roll analysis(vertical or horizontal) for report in SAS Programming

Roll analysis(vertical or horizontal) for report

Re: Roll analysis(vertical or horizontal) for report