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
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.
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.
Oh yes. Thank you how did I miss that before posting? It worked.
Regards,
Sam
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.