topic Using dataset from a permanent library in proc sql in SAS Programming

Using dataset from a permanent library in proc sql

Mahip — Wed, 03 Jan 2018 06:46:59 GMT

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.

Re: Using dataset from a permanent library in proc sql

novinosrin — Wed, 03 Jan 2018 07:05:45 GMT

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

Re: Using dataset from a permanent library in proc sql

Mahip — Wed, 03 Jan 2018 07:23:17 GMT

Here is the log:

Re: Using dataset from a permanent library in proc sql

novinosrin — Wed, 03 Jan 2018 07:31:31 GMT

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

Re: Using dataset from a permanent library in proc sql

Kurt_Bremser — Wed, 03 Jan 2018 07:37:25 GMT

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.

Re: Using dataset from a permanent library in proc sql

novinosrin — Wed, 03 Jan 2018 07:40:35 GMT

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;