topic Re: Pad out dataset with new rows in SAS Programming

Pad out dataset with new rows

Jamie_H — Mon, 15 May 2017 15:57:29 GMT

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

Re: Pad out dataset with new rows

Shmuel — Mon, 15 May 2017 16:16:45 GMT

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;

run;

Re: Pad out dataset with new rows

novinosrin — Mon, 15 May 2017 16:33:41 GMT

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;

run;

Regards,

Naveen Srinivasan

Re: Pad out dataset with new rows

Shmuel — Mon, 15 May 2017 16:36:08 GMT

@novinosrin you are absolutly right.

thank you.

Re: Pad out dataset with new rows

Reeza — Mon, 15 May 2017 17:07:09 GMT

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.

Re: Pad out dataset with new rows

ballardw — Mon, 15 May 2017 17:18:20 GMT

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;