I received some rather complicated SQL code from our database group. I input it into SAS PROC SQL properly, and it works. Great! But I don't understand it.
What I expect to see something like the following constructions in the FROM Clause
FROM table1 RIGHT JOIN table2 ON table1.lotid=table2.lotid
LEFT JOIN table3 ON table1.historyid=table3.historyid
What I expect to see, and the way I write code, looks like the above. The key point I am making above is that each join has its own ON clause.
But that's not what I see in this working example of SQL
I see tables being joined with no apparent ON clause in the sequence that I displayed above, note that many joins are done in sequence and only after the third join do I see an ON clause. The code below is a fragment of the entire FROM clause. Please explain this to me.
FROM db.StartHistoryDetail RIGHT OUTER JOIN db.Employee Employee_1 INNER JOIN db.Product
INNER JOIN db.ProductBase ON db.Product.ProductBaseId = db.ProductBase.ProductBaseId
INNER JOIN db.HistoryMainline INNER JOIN db.Container
ON db.HistoryMainline.ContainerId = db.Container.ContainerId
I find it puzzling too, The following example generates an error at the missing ON clause:
data a;
do x = "A", "B", "C"; output; end; run;
data b;
do x = "R", "S"; output; end; run;
data c;
do x = "1", "2", "S"; output; end; run;
proc sql;
select * from a inner join b inner join c on b.x=c.x;
quit;
PG
Paige,
I get an error if I run that code. Can you post the full code that actually ran?
Art
My guess is that your query is not run by SAS, it is passed to some other DBMS as a pass-through query. That DBMS could possibly understand the joins without ON clauses as NATURAL joins. Natural joins have implicit ON clauses that equate all field pairs with corresponding names and types. Going back to my small example, an equivalent SAS/SQL query would be :
data a;
do x = "A", "B", "R"; a + 1; output; end; run;
data b;
do x = "R", "B"; b + 1; output; end; run;
data c;
do x = "1", "2", "R"; c + 1; output; end; run;
proc sql;
select * from a natural inner join b inner join c on b.x=c.x;
quit;
PG
Yes, the SQL is passed to an SQL Server for execution.
If, as you are all telling me, that this is not a SAS issue but rasther an issue about how the code works on an SQL Server, I think the best answer for me is to go back to the database group that created the code and see if any of them can explain it.
If I get an explanation, I will post it here.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.