- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Please post data in usable form.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;