topic Re: why is libref not required for On clause in Proc SQL ? in Programming 1 and 2

why is libref not required for On clause in Proc SQL ?

palvani — Wed, 26 May 2021 13:42:32 GMT

While querying the data using Proc SQL from two tables without using aliases, the program seems to run fine without using libref in On clause. How does SAS recognize if the table we are referring to is from work or a particular library(pg1 in the below example)?

Ex:

proc sql;
select Season, Name, storm_summary.Basin, BasinName, MaxWindMPH
from pg1.storm_summary inner join pg1.storm_basincodes
on storm_summary.Basin = storm_basincodes.Basin
order by Season desc, Name;
quit;

Re: why is libref not required for On clause in Proc SQL ?

Al14 — Wed, 26 May 2021 14:32:16 GMT

The ON clause can only access tables from the FROM clause. In your example, pg1.storm_summary and pg1.storm_basinCodes are the only tables the ON clause considers

Re: why is libref not required for On clause in Proc SQL ?

Tom — Wed, 26 May 2021 14:40:43 GMT

In an ON clause you are referencing VARIABLES, not DATASETS.

So depending where you use X.Y it means different things.

In SQL code referencing a variable the X value (called an ALIAS) specifies the dataset and the Y value specifies the variable in that dataset.

In SAS code referencing a dataset the X value (called a LIBREF) specifies the library and the Y value specifies the dataset (called a MEMBER) in that library.

Re: why is libref not required for On clause in Proc SQL ?

Kurt_Bremser — Wed, 26 May 2021 15:20:39 GMT

If you were joining datasets with the same name from different libraries, then you would have to define aliases in the FROM clause and use these in the ON.