topic Re: Complicated merging question in SAS Programming

Complicated merging question

epigrad123 — Wed, 20 Apr 2016 21:54:13 GMT

I have a complicated merging question that may not even be possible in SAS. I have two datasets on testing results for a certain condition. One has names of patient, dob, date of visit, site number, and client ID. The other one has dob, date of visit, site number, and client id in addition to risk behavior questions,demographic information, and test results.

I want to merge these two datasets to get one observation per each visit for each client, and also have a way to display this information in wide format when I need to do longitudinal analysis . The only thing is, there is a chance that the client ID has been used on multiple people (not that often, but a few instances of it) This is usually because different testing sites might both assign the same number for the respecitve patients. This means I need to merge not only on client ID, but DOB (assuming the chance that two people who have the same ID had the same DOB is close to 0), site number, and date of visit as well.

Any ideas on how to execute this?

Re: Complicated merging question

ballardw — Wed, 20 Apr 2016 22:12:50 GMT

This is likely going to be a job for Proc SQL in any form.

If the variables you need to match on have the same names in both sets a NATURAL JOIN may work.

proc sql;
   create table want as
   select setone.*, settwo.* 
   from setone natural join settwo;
quit;

or use a specific join criteria

proc sql;
   create table want as
   select setone.*, settwo.* 
   from setone  join settwo
      on setone.id=settwo.id and setone.dob=settwo.dob 
         and setone.site=settwo.site and setone.visitdate=settwo.visitdate
quit;

Re: Complicated merging question

Ksharp — Thu, 21 Apr 2016 01:29:47 GMT

You'd better post some data and the output you want to see . Otherwise, we all just guessing what is your purpose.

Re: Complicated merging question

Reeza — Thu, 21 Apr 2016 05:53:00 GMT

It sounds like fuzzy matching. Take a look at the link king software, and apparently they have the SAS code available as well.