Obsidian | Level 7

## How to get range of date between start and end date

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: How to get range of date between start and end date

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.

4 REPLIES 4
Super User

## Re: How to get range of date between start and end date

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.

Obsidian | Level 7

## Re: How to get range of date between start and end date

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

Regards,

Sam

Super User

## Re: How to get range of date between start and end date

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
```
Obsidian | Level 7

## Re: How to get range of date between start and end date

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

Regards,
Sam
Discussion stats
• 4 replies
• 648 views
• 2 likes
• 3 in conversation