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?
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;
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;
You'd better post some data and the output you want to see . Otherwise, we all just guessing what is your purpose.
It sounds like fuzzy matching. Take a look at the link king software, and apparently they have the SAS code available as well.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.