BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Beto16
Obsidian | Level 7

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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

3 REPLIES 3
Reeza
Super User

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.

PGStats
Opal | Level 21

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
Beto16
Obsidian | Level 7

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 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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