BookmarkSubscribeRSS Feed
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
4 REPLIES 4
PGStats
Opal | Level 21

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

PG
art297
Opal | Level 21

Paige,

I get an error if I run that code.  Can you post the full code that actually ran?

Art

PGStats
Opal | Level 21

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

PG
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller

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!

How to Concatenate Values

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.

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
  • 4 replies
  • 1000 views
  • 3 likes
  • 3 in conversation