Help using Base SAS procedures

Left join not working

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

Left join not working

Hi I have two tables. That I want to do a left join  and its bringing in only data where it's equal  enclose is an example of code 

 

proc sql;

create table temp2 as

select table1.*, table2.id , table2.date

from table1,table2

where table1.id=table2.id and table2.date Gt table1.date

group by table1.id

having table2.date-table1.date=min(table2.date-table1.date);

 

create table want as

select temp2.*,table2.id ,table2. Date

from temp2,table2

where temp2.id=table2.id and table2.date gt temp2.date

group by temp2.id

having table2.date-temp2.date=min(table2.date-temp2.date)

quit;

 

i did did use the left join it didn't work  thanks for assistance 

 


Accepted Solutions
Solution
‎03-09-2016 04:18 PM
Respected Advisor
Posts: 4,651

Re: Left join not working

The problem is that in a left join, non matching table2 records will show table2.id and table2.date as missing which will cause the having clause to exclude them.  You can try this instead:

 

proc sql;
create table temp2 as
select 
	table1.*, 
	table2.id as id2, 
	table2.date as date2
from 
	table1 left join
	table2 on table1.id=table2.id and table2.date gt table1.date
group by table1.id
having coalesce(table2.date,table1.date)-table1.date=min(coalesce(table2.date,table1.date)-table1.date);
PG

View solution in original post


All Replies
Super User
Posts: 17,842

Re: Left join not working

None of the code below specifies a left join....

 

You also have a HAVING clause which filters data so it's not a 'straight left join'. Remove the having clause and see if you get what you expect.

Solution
‎03-09-2016 04:18 PM
Respected Advisor
Posts: 4,651

Re: Left join not working

The problem is that in a left join, non matching table2 records will show table2.id and table2.date as missing which will cause the having clause to exclude them.  You can try this instead:

 

proc sql;
create table temp2 as
select 
	table1.*, 
	table2.id as id2, 
	table2.date as date2
from 
	table1 left join
	table2 on table1.id=table2.id and table2.date gt table1.date
group by table1.id
having coalesce(table2.date,table1.date)-table1.date=min(coalesce(table2.date,table1.date)-table1.date);
PG
Frequent Contributor
Posts: 80

Re: Left join not working

Hi Pgstat,

i tried your code with coalesce  I notice you used comma instead of minus I tried both gave me small sample ..currently table 1 has 7k records I only get 84.... What I am doing in the code is if there is match table 1 and table 2 than from table two give the next date of service for example 

table 1 

 

id.        Date  

a1.        01/01/16 

 

table 2 

id.        Date 

a1.        01/02/16

a1.        01/06/16

i  want the left join to show 

Id.      Date.              Next service 

a1.    01/01/16            01/02/16

 

thats what I want to see what I see now is some thing like this

id.         Date.              Next service

a1.         01/1/16.            01/02/16

a1.        01/1/16.              01/06/16

a1.       01/1/16.              01/09/16

 

is every instance in table 2 is brought over ..hope that make sense thanks 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 505 views
  • 0 likes
  • 3 in conversation