topic Re: Populating missing months in SAS Programming

Populating missing months

Kiteulf — Wed, 13 Mar 2019 09:13:53 GMT

I am trying to populate periods instead of having start and end periods.

Something like this if you get the idea?

DATA have;
INPUT obj $10. start end;
CARDS;
PA19460-1 201108 201202
PA19460-2 201202 201209
PA19460-3 201209 201705
PA19460-4 201705 201809
TR56351-1 201308 201408
TR56351-2 201408 201409
KJ92100-1 201101 201201
;
RUN;

DATA want;
INPUT obj $10. monthperiod ;
CARDS;
PA19460-1 201108
PA19460-1 201109
PA19460-1 201110
PA19460-1 201111
PA19460-1 201112
PA19460-1 201201
PA19460-1 201201
PA19460-1 201202
PA19460-2 201203
PA19460-2 201204
PA19460-2 201205
PA19460-2 201206
PA19460-2 201207
PA19460-2 201208
PA19460-2 201209
PA19460-3 201210
PA19460-3 201211
PA19460-3 201212
...etc
TR56351-1 201308
TR56351-1 201309
TR56351-1 201310
TR56351-1 201311
TR56351-1 201312
TR56351-1 201401
TR56351-1 201402
TR56351-1 201403
TR56351-1 201404
TR56351-1 201405
TR56351-1 201406
TR56351-1 201407
TR56351-1 201408
TR56351-2 201409
TR56351-2 201410

;
RUN;

Re: Populating missing months

MichaelLarsen — Wed, 13 Mar 2019 09:43:40 GMT

data want;
  set have;
  /* Convert Start and End to date values */
  Start_Date = input( left(start),yymmn6.);
  End_Date = input( left(end),yymmn6.);
  format Start_Date End_Date date9.;
  Months = intck('Month',Start_Date,End_Date)+1;
  do m=0 to Months;
    MonthPeriod = put(intnx('Month',Start_Date,m),yymmn6.);
    output;
  end;
  drop Months m Start_Date End_Date start end;
run;

I believe the above step should do what you want.

Re: Populating missing months

s_lassen — Wed, 13 Mar 2019 10:00:51 GMT

You can use a date calculation, as suggested by @MichaelLarsen , or you can do it fast and dirty:

data want;
  set have;
  do monthperiod=start by 1;
    if mod(monthperiod,100)=13 then
      monthperiod+88; /* new year, month=1 */
    if monthperiod>end then leave;
    output;
    end;
  drop start end;
run;

Re: Populating missing months

Kiteulf — Wed, 13 Mar 2019 10:57:00 GMT

Thanks

Just need to correct for having a double month at the end of each obj, monthperiod

Re: Populating missing months

Kurt_Bremser — Wed, 13 Mar 2019 11:41:22 GMT

Always use SAS dates, so you can make use of the relevant functions and formats:

data want;
set have;
monthperiod = input(put(start,6.),yymmn6.);
format monthperiod yymmn6.;
output;
do while (monthperiod <= input(put(end,6.),yymmn6.));
  monthperiod = intnx('month',monthperiod,1,'b');
  output;
end;
keep obj monthperiod;
run;