topic Proc SQL left join missing values in SAS Programming

Proc SQL left join missing values

rhachey — Thu, 20 Jul 2017 14:45:17 GMT

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!

Re: Proc SQL left join missing values

Reeza — Thu, 20 Jul 2017 14:48:20 GMT

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;

Re: Proc SQL left join missing values

rhachey — Thu, 20 Jul 2017 16:33:04 GMT

Hi Reeza, Thank you for the quick reply. Any thoughts on how I can edit the left join to achieve the results I want?

Re: Proc SQL left join missing values

kiranv_ — Thu, 20 Jul 2017 18:33:31 GMT

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;