Using dataset from a permanent library in proc sql

[Mahip]

I am using proc sql to combine my datasets. One of my datasets - a large one - is stored in permanent library. When I use proc sql with the following code, an error message shows up and the system stops processing.

proc sql;

create table XYZ as

select *

from Project.ABC inner join DEF

on Project.ABC.id=DEF.id

order by DEF.id;

quit;

I am not sure whether it is possible to use a permanently stored dataset in proc sql. When I remove the Project. (my library name) from the on statement, it says the file does not exist.

[novinosrin]

it is certainly possible and absolute to use a permanent sas dataset with a two level name i.e libref.dataset name in proc sql. Please post the log here in the forum

[Mahip]

Here is the log:

[novinosrin]

Got it.

Remove the libname in the on clause and run again. Keep that as a two level name such as membername.columnname

like: on record.id=record2.id

[Kurt_Bremser · Accepted solution]

DO NOT POST LOGS IN PICTURES!

Logs are TEXT(!) and should therefore posted as such, so that their contents can easily be copy/pasted for reference, web searching and testing.

Use the {i} icon to post log text.

You should use table aliases and use those to reference your columns, thereby avoiding a three-stage name.

Your use of the asterisk in the select will cause a WARNING for the already existing variable id; don't use the asterisk, use an exhaustive list of the wanted variables instead.

[novinosrin]

Use table alias as suggested by Kurt

proc sql;

create table XYZ as

select *

from Project.ABC a inner join DEF b

on a.id=b.id

order by b.id;

quit;