So we have one dataset:
Start_date End_date Month_Interval
01JAN2001 31DEC2002 6
with 'Start_date', 'End_date' having date values, and 'Month_Interval' having numeric values.
We want to create a new dataset based on the data above:
Time_Period
1/2001 - 6/2001
7/2001 - 12/2001
1/2002 - 6/2002
7/2002 - 12/2002
with 'Time_Period' having character values.
Basically the interval starts on '01JAN2001', there are 6 months in each interval, and the period ends on '31DEC2002'.
How should we achieve this?
data have; infile cards dsd dlm=','; input start_date : date9. end_date : date9. interval; format start_date end_date date9.; cards; 01JAN2001,31DEC2002,6 ; run; data want; set have; do while(end lt end_date); end=intnx('month',start_date,interval-1,'e');output; start_date=intnx('month',end,1); end; format start_date end mmyys7.; drop end_date; run;
How about this?
data have;
infile cards dsd dlm=',';
attrib start_date end_date length=4 informat=date9. format=monyy7.;
attrib interval length=3;
input start_date
end_date
interval;
cards;
01JAN2001,31DEC2002,6
;
run;
data want;
set have;
length time_period $ 17;
do i = start_date to end_date by interval * 31;
i = intnx('month', i, 0, 'b');
j = intnx('month', i, interval - 1, 'b');
time_period = translate(catx(' - ', putn(i, 'mmyy7.'), putn(j, 'mmyy7.')), '/', 'M');
output;
end;
keep time_period;
run;
The format mmyy returns mmMyyyy, so the translate function swaps that out for a slash. I could have used something cleverer than interval * 31, but since I reset i to the beginning of the incremented month on the very next line, this will always work; sometimes (often) it's best to keep it simple, and it's easily clear enough what's going on.
data have; infile cards dsd dlm=','; input start_date : date9. end_date : date9. interval; format start_date end_date date9.; cards; 01JAN2001,31DEC2002,6 ; run; data want; set have; do while(end lt end_date); end=intnx('month',start_date,interval-1,'e');output; start_date=intnx('month',end,1); end; format start_date end mmyys7.; drop end_date; run;
data have;
length start_date end_date month_interval 8;
input start_date end_date month_interval;
informat start_date end_date date9.;
format start_date end_date date9.;
datalines;
01JAN2001 31DEC2002 6
;
run;
data want;
length time_period $20;
set have;
start=start_date;
do until (end ge end_date);
end=intnx("month",start,month_interval-1,"E");
time_period=catx(" - ",put(start,mmyys7.),put(end,mmyys7.));
output;
start=intnx("month",end,1,"B");
end;
format start end date9.;
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!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.