BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pink_poodle
Barite | Level 11

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Angel_Larrion
SAS Employee

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;

 

View solution in original post

5 REPLIES 5
mkeintz
PROC Star

Please provide, in the form of working data step, the two starting data sets, and what the resulting data set should look like.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
pink_poodle
Barite | Level 11
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!

 

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
pink_poodle
Barite | Level 11

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

 

Angel_Larrion
SAS Employee

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;

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 597 views
  • 2 likes
  • 3 in conversation