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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.