I am using SAS Enterprise Guide Version 7.13 to try to do the following:
I have a table with a list of specific IDs and visit dates and I would like to join this table with the master list to find all visits following the initial visit.
TableA:
PatientID InitialVisit
1 1/1/2016
2 3/12/2016
3 4/15/2016
TableB:
MemberID Date
1 12/14/2015
1 1/1/2016
1 2/25/2016
2 3/12/2016
2 4/4/2016
2 7/1/2016
3 11/7/2015
3 12/18/2015
3 4/15/2016
My code:
proc sql;
create table test as
select a.*
,b.Date
from TableA as a
left join TableB as b
on a.PatientID=b.MemberID and date>InitialVisit
;quit;
What I want is:
PatientID InitialVisit Date
1 1/1/2016 2/25/2016
2 3/12/2016 4/4/2016
2 3/12/2016 7/1/2016
3 4/15/2016 .
What I am getting is:
1 1/1/2016 2/25/2016
2 3/12/2016 4/4/2016
2 3/12/2016 7/1/2016
Since I am using a Left Join, I would expect all values from TableA to exist in the new table, but what I am getting is more like an Inner Join. Any help would be much appreciated!
Your second condition is likely filtering the records out. If you want a left join you need to change how you filter the join.
proc sql;
create table test as
select a.*
,b.Date
from TableA as a
left join TableB as b
on a.PatientID=b.MemberID and date>InitialVisit
;quit;
Hi Reeza, Thank you for the quick reply. Any thoughts on how I can edit the left join to achieve the results I want?
I am getting exact answer you want by running your query
data TableA; input PatientID InitialVisit:mmddyy10.; format InitialVisit mmddyy10.; datalines; 1 1/1/2016 2 3/12/2016 3 4/15/2016 ; data tableb; infile datalines truncover; input MemberID Date:mmddyy10.; format date mmddyy10.; datalines; 1 12/14/2015 1 1/1/2016 1 2/25/2016 2 3/12/2016 2 4/4/2016 2 7/1/2016 3 11/7/2015 3 12/18/2015 3 4/15/2016 ; proc sql; create table test as select a.* ,b.Date from TableA as a left join TableB as b on a.PatientID=b.MemberID and date>InitialVisit ;quit;
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.