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.
Many thanks
Try next code:
data want;
set have;
by DTE;
if last.dte and MOB < 60 then do;
call missing(balance_remaining);
do mob=mob+1 to 60;
output;
end;
end;
run;
I think you would need another explicit OUTPUT statement in @Shmuel 's code.
data want;
set have;
by DTE;
output;/*modified here*/
if last.dte and MOB < 60 then do;
call missing(balance_remaining);
do mob=mob+1 to 60;
output;
end;
end;
run;
Regards,
Naveen Srinivasan
@novinosrin you are absolutly right.
thank you.
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.
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.