Hello,
Please, I need help merging two sets of data by date and name. The first data set has the date patients were seen and the names of provider. The second data set has then date the providers started his/her shift and the names of the provider. The problem is some providers started their shift the day before the patient visited so those observations are left out of the merge. I need help including them in the merge.
Below is the sas code I used.
DATA Newdata;
MERGE New6 (in=a) Scheduling_data3;
BY Start_date provider; if a=1;
RUN;
Thank you.
proc sql;
create table Newdata as
select *
from New6, scheduling_data4
where New6.provider = scheduling_data4.provider
and New6.start_date between scheduling_data4.start_date and scheduling_data4.Shift_end_date;
quit;
I would simply join by provider and then check your dates after the merge has occurred.
proc sort data=New6; by provider;
run;
proc sort data=scheduling_data3; by provider;
run;
DATA NewData;
Merge New6(in=A) Scheduling_data3 (rename (start_date=prov_strt_dt)); by provider;
if A;
if start_date >= prov_strt_dt;
run;
You probably also want to put a check in for the provider end date as well.
The providers have multiple shifts so merging by provider only will not work.
Yes, I have the shift start time and date and the shift end time and date.
I will need sometime to work on the fake data. Please, how can I merge using SQL merge?
Thank you.
How familiar are you with SQL merges?
If not, I suggest reading some papers such as this:
https://www.lexjansen.com/pharmasug/2005/CodersCorner/cc26.pdf
In general though, you can merge on a range.
from table1 as t1
left join table2 as t2
t1.id = t2.id and
on t1.eventDateTiime between t2.startDateTime and t2.endDateTime;
@UcheOkoro wrote:
Yes, I have the shift start time and date and the shift end time and date.
I will need sometime to work on the fake data. Please, how can I merge using SQL merge?
Thank you.
If you have a many to many join, you can't use a merge statement. A merge doesn't actually join tables together like SQL does so you end up with unexpected results if you have a many to many join. You'll need to use proc sql.
proc sql;
create table New6 as
select N.*,
S.wanted_field1,
S.wanted_fiedl2
from New6 as N,
schduling_data3 as S
where N.provider = S.provider
and N.start_date between S.start_date and S.end_date;
quit;
This presupposes that you have an end date and that the provider time periods do not overlap between provider records. If not, you will end up with duplicate rows and you will need to make a determination of which provider row is the correct one. You may be able to do that in the proc sql step or you may want to sort your data and select it out using a data step.
@UcheOkoro wrote:
Hello,
The second data set has then date the providers started his/her shift and the names of the provider.
Do have the END date of the shift? Perhaps a start and end time? Or can you request it?
proc sql;
create table Newdata as
select *
from New6, scheduling_data4
where New6.provider = scheduling_data4.provider
and New6.start_date between scheduling_data4.start_date and scheduling_data4.Shift_end_date;
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 25. 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.