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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.