topic Re: expand a dataset of dates to include all days within each range in SAS Programming

expand a dataset of dates to include all days within each range

jogianni — Wed, 06 Apr 2016 16:45:53 GMT

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!

Re: expand a dataset of dates to include all days within each range

Astounding — Wed, 06 Apr 2016 17:43:04 GMT

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.

Re: expand a dataset of dates to include all days within each range

ChrisNZ — Wed, 06 Apr 2016 22:27:46 GMT

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