topic Re: Merging data by name and date in SAS Programming

Merging data by name and date

UcheOkoro — Tue, 20 Oct 2020 17:49:45 GMT

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.

Re: Merging data by name and date

Groucho — Tue, 20 Oct 2020 18:28:18 GMT

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.

Re: Merging data by name and date

UcheOkoro — Tue, 20 Oct 2020 19:18:40 GMT

The providers have multiple shifts so merging by provider only will not work.

Re: Merging data by name and date

Reeza — Tue, 20 Oct 2020 19:50:23 GMT

Do you have the start and end times of each shift?
If you could post some example data (fake data is entirely fine) that replicates your issues so we can work with a real example that would help a lot.
If you have the start and end times you can do this using SQL merge quite easily, if you don't it gets a bit harder as you may have to make generalizations that are not always true.

Re: Merging data by name and date

ballardw — Tue, 20 Oct 2020 20:06:44 GMT

@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?

Re: Merging data by name and date

UcheOkoro — Tue, 20 Oct 2020 20:24:06 GMT

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.

Re: Merging data by name and date

Reeza — Wed, 21 Oct 2020 01:16:07 GMT

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.

Re: Merging data by name and date

Groucho — Wed, 21 Oct 2020 12:12:57 GMT

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.

Re: Merging data by name and date

UcheOkoro — Wed, 21 Oct 2020 18:33:52 GMT

 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;