Desktop productivity for business analysts and programmers

Create time interval automatically

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

Create time interval automatically

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?


Accepted Solutions
Solution
‎03-08-2017 12:07 AM
Super User
Posts: 9,878

Re: Create time interval automatically


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;

View solution in original post


All Replies
Super Contributor
Posts: 252

Re: Create time interval automatically

[ Edited ]

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.

 

 

Solution
‎03-08-2017 12:07 AM
Super User
Posts: 9,878

Re: Create time interval automatically


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;

Super Contributor
Posts: 386

Re: Create time interval automatically

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 203 views
  • 1 like
  • 4 in conversation