BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ayin
Quartz | Level 8

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

3 REPLIES 3
LaurieF
Barite | Level 11

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.

 

 

Ksharp
Super User

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;

ScottBass
Rhodochrosite | Level 12
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;

Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

Creating Custom Steps in SAS Studio

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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