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

Hello, I am working on taking code from a program and building it out in GUI format for other SAS users who have limited understanding with reading programs.

 

I came across code similar to the following:

proc sql;

CREATE TABLE Gridwork.Test AS

 SELECT t1.ID1

                t2.ID2

                t1.First_Column,

                t2.Second_Column

FROM Gridwork.Dataset1 t1

LEFT JOIN Gridwork.Dataset2 v2 ON (t1.ID=t2.ID) AND (t2.ID <> "0")

WHERE t1.First_Column not is missing

QUIT;

 

My question is in regards to the "AND" within the Left Join statement.  Is it the same as just taking t2.<>"0" and putting it in the where statement similar to below?

 

CREATE TABLE Gridwork.Test AS

 SELECT t1.ID1

                t2.ID2

                t1.First_Column,

                t2.Second_Column

FROM Gridwork.Dataset1 t1

LEFT JOIN Gridwork.Dataset2 v2 ON (t1.ID=t2.ID)

WHERE t1.First_Column not is missing AND t2.ID<>"0"

QUIT;

 

I'm unsure of how to make that "t2.ID<>"0" distinction within the "Tables and Joins" section of SAS EG.  There only seems to be the ability to pick the join, join order and that's it. Any "and" functionality without programming seems to lead itself to a "Where" clause but that looks different than the code.

 

Hope I explained this well and thanks for any help!  

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

The location of the condition (ON clause vs WHERE clause) can make a difference for LEFT joins. The ON clause is evaluated during the join, when the condition involves the right table and is not met, the record is kept in the results with the right side columns set to missing. When the condition figures in the WHERE clause, it is evaluated after the join. Right side columns enter the condition as missing and may cause the record to be excluded from the result set. An example:

 

data a;
do r = "A", "B", "C";
    output;
    end;
run;

data b;
r = "A"; x = 1; output;
r = "C"; x = 3; output;
run;

proc sql;
title "Condition in join";
select a.r, b.x
from a left join b on a.r=b.r and x > 0;
title "Condition in where";
select a.r, b.x
from a left join b on a.r=b.r
where x > 0;
quit;
Condition in join
r 	x
A 	1
B 	.
C 	3

Condition in where
r 	x
A 	1
C 	3

when the condition is in the WHERE clause, the value of x (missing) is compared to 0 and the record is rejected.

PG

View solution in original post

2 REPLIES 2
PGStats
Opal | Level 21

The location of the condition (ON clause vs WHERE clause) can make a difference for LEFT joins. The ON clause is evaluated during the join, when the condition involves the right table and is not met, the record is kept in the results with the right side columns set to missing. When the condition figures in the WHERE clause, it is evaluated after the join. Right side columns enter the condition as missing and may cause the record to be excluded from the result set. An example:

 

data a;
do r = "A", "B", "C";
    output;
    end;
run;

data b;
r = "A"; x = 1; output;
r = "C"; x = 3; output;
run;

proc sql;
title "Condition in join";
select a.r, b.x
from a left join b on a.r=b.r and x > 0;
title "Condition in where";
select a.r, b.x
from a left join b on a.r=b.r
where x > 0;
quit;
Condition in join
r 	x
A 	1
B 	.
C 	3

Condition in where
r 	x
A 	1
C 	3

when the condition is in the WHERE clause, the value of x (missing) is compared to 0 and the record is rejected.

PG
mmagnuson
Quartz | Level 8
Thanks for the example. This has made a lot of sense and after re-visiting documentation on joins in SAS EG I found the "edit" button in the GUI section of the joins and tables where I can add in those ON statements. Much appreciated!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 2 replies
  • 556 views
  • 0 likes
  • 2 in conversation