How to get range of date between start and end date

[twix17]

Hi everyone,

I have this table with a start and end date but I want a series of dates between the two dates as seen below.

Data have

id	start_date	end_date
1	1-Jul-17	1-Jul-17
2	1-Aug-17	1-Feb-18

Data want

id	start_date	end_date	range
1	1-Jul-17	1-Nov-17	1-Jul-17
1	1-Jul-17	1-Nov-17	1-Aug-17
1	1-Jul-17	1-Nov-17	1-Sep-17
1	1-Jul-17	1-Nov-17	1-Oct-17
1	1-Jul-17	1-Nov-17	1-Nov-17
2	1-Aug-17	1-Feb-18	1-Aug-17
2	1-Aug-17	1-Feb-18	1-Sep-17
2	1-Aug-17	1-Feb-18	1-Oct-17
2	1-Aug-17	1-Feb-18	1-Nov-17
2	1-Aug-17	1-Feb-18	1-Dec-17
2	1-Aug-17	1-Feb-18	1-Jan-18
2	1-Aug-17	1-Feb-18	1-Feb-18

Thanks

[ballardw · Accepted solution]

First question: Are your dates SAS date values? I ask because that will be needed to solve this. I also ask because the values you display are not in any of the "standard" date formats SAS uses.

Second, you have to explain a lot of logic about how the output is to be generated. I cannot see any obvious way that

id	start_date	end_date
1	1-Jul-17	1-Jul-17

generates enddates of 1 Nov 17 or "range" values in Aug, Sep, Oct of Nov.

If you meant the end_date to be 1-Nov-17 then perhaps:

data have;
   input id	start_date :date9.	end_date : date9.;
   format start_date end_date date9.;
datalines;
1	1-Jul-17	1-Nov-17
2	1-Aug-17	1-Feb-18
;

data want;
   set have;
   range = start_date;
   do until (range > end_date);
      output;
      range= intnx('month',range,1,'b');
   end;
   format range date9.;
run;

Note the data step creating HAVE as the way to show example data. Which has been mentioned before.

The Intnx function increments date values. So the above add a date value for the beginning of a month until it exceeds the end date and uses the OUTPUT statement to control when the values are written to the output data set.

[twix17]

Oh yes. Thank you how did I miss that before posting? It worked. 

Regards,

Sam

[Tom]

So you have data like this?

data have;
  input id start_date :date. end_date :date. ;
  format start_date end_date date9. ;
cards;
1 01JUL2017 01NOV2017
2 01AUG2017 01FEB2018
;

You can use INTNX() to increment by month.  You can use INTCK() to determine how many months to output.

data want;
  set have;
  do offset=0 to intck('month',start_date,end_date);
    date = intnx('month',start_date,offset);
    output;
  end;
  format date date9.;
run;

NOTE: I changed the name of the new variable to DATE as RANGE did not make any sense for a single date value.

Result:

                start_
Obs    id         date     end_date    offset         date

  1     1    01JUL2017    01NOV2017       0      01JUL2017
  2     1    01JUL2017    01NOV2017       1      01AUG2017
  3     1    01JUL2017    01NOV2017       2      01SEP2017
  4     1    01JUL2017    01NOV2017       3      01OCT2017
  5     1    01JUL2017    01NOV2017       4      01NOV2017
  6     2    01AUG2017    01FEB2018       0      01AUG2017
  7     2    01AUG2017    01FEB2018       1      01SEP2017
  8     2    01AUG2017    01FEB2018       2      01OCT2017
  9     2    01AUG2017    01FEB2018       3      01NOV2017
 10     2    01AUG2017    01FEB2018       4      01DEC2017
 11     2    01AUG2017    01FEB2018       5      01JAN2018
 12     2    01AUG2017    01FEB2018       6      01FEB2018

[twix17]

Thank you. Yes i wanted the end date for the first row to be 01NOV2017.

Regards,
Sam