Proc SQL left join missing values

[rhachey]

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!

[Reeza]

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;

[rhachey]

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

[kiranv_]

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;