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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: