topic Re: Join with AND function in SAS Enterprise Guide

Join with AND function

mmagnuson — Wed, 03 Jun 2020 16:53:06 GMT

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!

Re: Join with AND function

PGStats — Wed, 03 Jun 2020 17:36:45 GMT

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.

Re: Join with AND function

mmagnuson — Wed, 03 Jun 2020 17:45:50 GMT

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!