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.

sas-innovate-2024.png

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.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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