DATA Step, Macro, Functions and more

Proc SQL left join missing values

Reply
New Contributor
Posts: 2

Proc SQL left join missing values

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!

Super User
Posts: 17,796

Re: Proc SQL left join missing values

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;

New Contributor
Posts: 2

Re: Proc SQL left join missing values

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

PROC Star
Posts: 252

Re: Proc SQL left join missing values

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;
Ask a Question
Discussion stats
  • 3 replies
  • 86 views
  • 0 likes
  • 3 in conversation