Complicated merging question

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Complicated merging question

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?


Accepted Solutions
Solution
‎04-21-2016 12:17 PM
Super User
Posts: 10,538

Re: Complicated merging question

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; 

View solution in original post


All Replies
Solution
‎04-21-2016 12:17 PM
Super User
Posts: 10,538

Re: Complicated merging question

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; 
Super User
Posts: 9,687

Re: Complicated merging question

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

Super User
Posts: 17,907

Re: Complicated merging question

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 283 views
  • 0 likes
  • 4 in conversation