so I have a list of dates, and for merging purposes I would like to get all the days inbetween each one. example:
current dateset:
date
12/16/2016 |
12/23/2016 |
12/30/2016 |
01/06/2017 |
Desired Dataset:
date days
12/16/2016 | 12/16/2016 |
12/16/2016 | 12/17/2016 |
12/16/2016 | 12/18/2016 |
12/16/2016 | 12/19/2016 |
12/16/2016 | 12/20/2016 |
12/16/2016 | 12/21/2016 |
12/16/2016 | 12/22/2016 |
12/23/2016 | 12/23/2016 |
12/23/2016 | 12/24/2016 |
12/23/2016 | 12/25/2016 |
12/23/2016 | 12/26/2016 |
12/23/2016 | 12/27/2016 |
12/23/2016 | 12/28/2016 |
12/23/2016 | 12/29/2016 |
12/30/2016 | 12/30/2016 |
12/30/2016 | 12/31/2016 |
12/30/2016 | 1/1/2017 |
12/30/2016 | 1/2/2017 |
12/30/2016 | 1/3/2017 |
12/30/2016 | 1/4/2017 |
12/30/2016 | 1/5/2017 |
Thanks!
This may be simplest:
data want;
set have;
set have (firstobs=2 rename=(date = next_date));
do days = date to next_date - 1;
output;
end;
format days mmddyys10.;
drop next_date;
run;
There are some tricky aspects, but at least the program is short.
Like this?
data HAVE;
input D mmddyy10.;
cards;
12/16/2016
12/23/2016
12/30/2016
01/06/2017
run;
data WANT;
set HAVE;
CURRENT=D;
D=lag(D);
if _N_>1 then do D1=D to CURRENT-1;
output;
end;
keep D D1;
run;
proc print;
format D D1 date9.;
run;
Obs | D | D1 |
---|---|---|
1 | 16DEC2016 | 16DEC2016 |
2 | 16DEC2016 | 17DEC2016 |
3 | 16DEC2016 | 18DEC2016 |
4 | 16DEC2016 | 19DEC2016 |
5 | 16DEC2016 | 20DEC2016 |
6 | 16DEC2016 | 21DEC2016 |
7 | 16DEC2016 | 22DEC2016 |
8 | 23DEC2016 | 23DEC2016 |
9 | 23DEC2016 | 24DEC2016 |
10 | 23DEC2016 | 25DEC2016 |
11 | 23DEC2016 | 26DEC2016 |
12 | 23DEC2016 | 27DEC2016 |
13 | 23DEC2016 | 28DEC2016 |
14 | 23DEC2016 | 29DEC2016 |
15 | 30DEC2016 | 30DEC2016 |
16 | 30DEC2016 | 31DEC2016 |
17 | 30DEC2016 | 01JAN2017 |
18 | 30DEC2016 | 02JAN2017 |
19 | 30DEC2016 | 03JAN2017 |
20 | 30DEC2016 | 04JAN2017 |
21 | 30DEC2016 | 05JAN2017 |
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.