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;
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.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.