topic Left join not working in SAS Procedures

Left join not working

Beto16 — Sat, 05 Mar 2016 17:47:44 GMT

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

Re: Left join not working

Reeza — Sat, 05 Mar 2016 18:03:48 GMT

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.

Re: Left join not working

PGStats — Sat, 05 Mar 2016 21:07:20 GMT

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);

Re: Left join not working

Beto16 — Mon, 07 Mar 2016 05:48:24 GMT

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