05-15-2017 11:57 AM
I have a program that creates a proc report at the end. This shows a cohort of data with the variable names in the define statement as 1 to 60 (these are created via a transpose earlier in the process, which is how they are named as a numeric).
However, depending on what criteria a user has selected earlier in the process, it may be there are not 60 months of data to report on. When this happens, sas obviously falls over.
To get around this, I want to try and identify the maximum number of months data I do have in my dataset (variable name MOB) and when this is less than 60, add a new row with the next MOB number. Repeat until you have 60 rows of data.
My dataset looks like this...
DTE MOB Balance_Remaining
01/01/16 0 0.97
01/01/16 1 0.95
01/01/16 2 0.92
01/01/16 3 0.91
01/02/16 0 0.95
01/02/16 1 0.89
01/02/16 2 0.82
01/02/16 3 0.77
Does anyone know a simple way of running through the MOB by DTE and adding in a row with a missing Balance_Remaining? In the above example, the code would need to add from MOB 4 to MOB 60.
05-15-2017 12:33 PM
I think you would need another explicit OUTPUT statement in @Shmuel 's code.
if last.dte and MOB < 60 then do;
do mob=mob+1 to 60;
05-15-2017 01:07 PM
Create a master dataset with all the dates/MOB needed for every report. In the step before you're proc report you can merge that dataset with the one you have. Then run the proc report without issues.
05-15-2017 01:18 PM
This is one way of implementing @Reeza's master data file approach. Dataset below represents the name of you dataset that needs the extra rows. If there are other variables needed from dataset then add them to the select statement in the final SQL step.
proc sql; create table base as selecte distinct dte from dataset ; quit; data need; set base; do mob=0 to 60; output; end; run; proc sql; create table want as select a.dte,a.mob,b.balance_remaining from need as a left join dataset as b on a.dte=b.dte and a.mob=b.mob ; quit;