BookmarkSubscribeRSS Feed
Jamie_H
Fluorite | Level 6

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

5 REPLIES 5
Shmuel
Garnet | Level 18

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;

novinosrin
Tourmaline | Level 20

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

Shmuel
Garnet | Level 18

@novinosrin you are absolutly right. 

thank you.

Reeza
Super User

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.

 

ballardw
Super User

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 937 views
  • 0 likes
  • 5 in conversation