DATA Step, Macro, Functions and more

Pad out dataset with new rows

Reply
Occasional Contributor
Posts: 9

Pad out dataset with new rows

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

Trusted Advisor
Posts: 1,556

Re: Pad out dataset with new rows

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;

PROC Star
Posts: 283

Re: Pad out dataset with new rows

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

Trusted Advisor
Posts: 1,556

Re: Pad out dataset with new rows

Posted in reply to novinosrin

@novinosrin you are absolutly right. 

thank you.

Super User
Posts: 19,785

Re: Pad out dataset with new rows


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.

 

Super User
Posts: 11,343

Re: Pad out dataset with new rows

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;
Ask a Question
Discussion stats
  • 5 replies
  • 132 views
  • 0 likes
  • 5 in conversation