Merge two data sets based on more than one variable

[mayasak]

Hi,

I have two data sets that I need to merge Set1 and Set2. Matching variables that can be used are MRN Full_Name DOB and specimen_Date. The issue that I have is that set1 have about one third MRNs (complete in set2) missing. Set 2 have more than 1/3 missing Full_name (complete in set2).

More than one person (obs) may have the same DOB and/or specimen_Date within each data set. So I'm leaning on using MRN and Full_Name but I'm not sure how to address the issue of missing data!

I'm wonderin if this works:

data merged;

merge  set1

            set2  (in = in2);

by mrn full_name;

if in2;

run;

Thank you

[ballardw]

First suggestion: Try it an see the result.

Second suggestion: make the sets small enough that you can check the results fairly quickly. If you have 1,000s of observations in both you might miss the needed behaviors.

The rules for how missing data are treated are best demonstrated by creating limited data sets, with missing in some of the NON-by variables and seeing the result. Which data is missing?

DOB and things like "specimen_date" in general are crappy matching variables unless there are other identification variables. Specimen_date in general I would say is likely not actually very useful for matching as it is extremely likely that the same person has multiple specimen_date values unless the specific purpose of the match is "test result" to specimen. I know that when I was going through some serious medical issues that "specimen_date" for some measures was sometimes 4 times per day and I don't want to image what the results of attempting to match that data with other things would result in.