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
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);
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.
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);
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.