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!
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.
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.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.