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;
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;
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.
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;
Thanks
Just need to correct for having a double month at the end of each obj, monthperiod
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.