Hi,
In one data set I have datetime (dt) ranges for hospital days (hday). I another dataset I have datetimes of events. How do I merge the events to hospital days?:
(hday, start_dt, end_dt) X (event_dt) -> the event happened on which hospital day?
Thank you!
you could use proc sql to do that:
proc sql;
create table output as
select d1.hday, d1.start, d1.end, d2.event
from d2 left join d1 on d1.start<=d2.event<=d1.end;
quit;
Please provide, in the form of working data step, the two starting data sets, and what the resulting data set should look like.
Here you go:
data d1; infile datalines delimiter=' '; input hday start end; datalines; 1 1 3
2 4 6
3 7 9 ;
run;
data d2;
infile datalines delimiter=' ';
input event;
2
3
5
8
;
run;
output:
hday start end event
1 1 3 2
1 1 3 3
2 4 6 5
3 7 9 8
Many thanks!
Thank you for provide sample data (you missed a datalines statement for d2).
This code works, and works quickly if your data sets are sorted chronologically and the time ranges in d1 do not overlap:
data d1;
infile datalines delimiter=' ';
input hday start end;
datalines;
1 1 3
2 4 6
3 7 9
;
run;
data d2;
infile datalines delimiter=' ';
input event;
datalines;
2
3
5
8
;
run;
data want;
if 0 then merge d1 d2 ;
set d1 (keep=start rename=(start=event) in=d1_start)
d2 (in=event_rec)
d1 (keep=end rename=(end=event) in=d1_end);
by event;
if d1_start then set d1;
if d1_end then call missing(hday,start,end);
if event_rec;
run;
If you have a d2 event outside of the d1 ranges, the observation will be recorded with missing hday, start, and end.
The "if 0 then merge" statement is there just to produce a desired variable order. Values would be unchanged if you remove it.
The key concept here is the "if d1_start then set d1" statement which reads in all the D! variables (unrenamed). They are automatically retained until the next time D!_START=1.
Thank you, this works, but my example was over-simplified. In reality, the start would be something like 2:45 pm, and end - 3:00 pm. I would like to capture an event = 2:55 pm. Is there a way to merge conditionally to hospital day if event is between start and end?:
hday start end event
1 2:45 3:00 2:55
you could use proc sql to do that:
proc sql;
create table output as
select d1.hday, d1.start, d1.end, d2.event
from d2 left join d1 on d1.start<=d2.event<=d1.end;
quit;
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: