topic Re: Merge Several Date Ranges in SAS Programming

Merge Several Date Ranges

DavidLie — Wed, 13 Jan 2021 04:13:17 GMT

Hi SAS Communities,

I have two datasets here:

(1) Have1: Showing all the date ranges. Example:

ID	Date_From	Date_To
1	21 Feb 2000	14 Mar 2000
1	23 Jun 2000	17 Jul 2000
1	18 Oct 2000	8 Nov 2000
....	...	...
2	29 Sept 2000	01 Jan 2001
...	...	...

(2) Have2: Listings of all dates. Example:

Date	Price
01 Jan 2000	x
02 Jan 2000	x
03 Jan 2000	x
....	...
31 Dec 2020	x

I would like to have a dataset that have the following:

Date	Price	ID1	ID2
01 Jan 2000	x	0	0
02 Jan 2000	x	0	0
03 Jan 2000	x	0	0
....	...	...	...
21 Feb 2000	x	1	0
22 Feb 2000	x	1	0
...	...	...	...
14 Mar 2000	x	1	0
15 Mar 2000	x	0	0
16 Mar 2000	x	0	0
...	...	...	...
31 Dec 2020	x	0	0

In short, the dataset above has the binary code such that the date that falls within the range will be 1, otherwise 0.

Best,

David

Re: Merge Several Date Ranges

andreas_lds — Wed, 13 Jan 2021 05:31:28 GMT

Please post data in usable form.

Re: Merge Several Date Ranges

Shmuel — Wed, 13 Jan 2021 06:56:59 GMT

In 1st table you posted are IDs 1, 2 - are there more?

Are IDs sequential (1 2 3 ...) or any numeric values?

Does ID1 of table-2 relate to ID=1 of table-1, ID2 to id=2 etc? Or did you mean something else?

Re: Merge Several Date Ranges

PeterClemmensen — Wed, 13 Jan 2021 07:30:43 GMT

See if you can use this as a template.

Feel free to ask 🙂

/* Example Data */
data have;
input ID (Date_From Date_To)(:date9.);
format Date: date9.;
datalines;
1 21Feb2000 14Mar2000
1 23Jun2000 17Jul2000
1 18Oct2000 08Nov2000
2 11Mar2000 14Mar2000
2 13Jun2000 17Aug2000
2 11Oct2000 09Nov2000
3 21Feb2000 14Mar2000
3 23Jun2000 17Jul2000
3 18Oct2000 08Nov2000
;

data price;
   do date = "01jan2000"d to "31dec2000"d;
      price = ceil(rand('uniform')*100);
      output;
   end;
   format date date9.;
run;

/* Find Largest ID */
proc sql noprint;
   select max(ID) into :m separated by ' '
   from have;
run;

%put &=m.;

/* Create wanted data set */
data want;

   if _N_ = 1 then do;
      dcl hash h(dataset : "have", multidata : "Y");
      h.definekey("ID");
      h.definedata(all : "Y");
      h.definedone();
   end;

   set price;
   array i id1-id&m.;
   if 0 then set have;

   do over i;
      do while (h.do_over(key : _I_) = 0);
         if Date_From le date le Date_To then i = 1;
      end;
   end;
run;

Re: Merge Several Date Ranges

PhilC — Wed, 13 Jan 2021 14:11:00 GMT

In my opinion, this is a PROC TRANSPOSE question. I'm using an indexed dataset, MERGE could be used. I also believe hash table solution is a bit overkill:

data have;
input ID (Date_From Date_To)(:date9.);
format Date: date9.;
datalines;
1 21Feb2000 14Mar2000
1 23Jun2000 17Jul2000
1 18Oct2000 08Nov2000
2 11Mar2000 14Mar2000
2 13Jun2000 17Aug2000
2 11Oct2000 09Nov2000
3 21Feb2000 14Mar2000
3 23Jun2000 17Jul2000
3 18Oct2000 08Nov2000
;
data price (Index=(date /unique));
   do date = "01jan2000"d to "31dec2000"d;
      price = ceil(rand('uniform')*100);
      output;
   end;
   format date date9.;
run;
data temp;
  set have;
    drop Date_From	Date_To;
  do date=Date_From to Date_To;
    truth=1; /*a dummy variable*/
    set price key=date /unique;
    output;
  end;
proc sort;
	BY date price;
run;
PROC TRANSPOSE DATA=temp
	OUT=WORK.WANT(drop=_NAME_)
	PREFIX=ID
;
	BY date price;
	ID ID;
	VAR truth;
RUN;