Using dataset from a permanent library in proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Using dataset from a permanent library in proc sql

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.


Accepted Solutions
Solution
a week ago
Super User
Posts: 8,301

Re: Using dataset from a permanent library in proc sql

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
PROC Star
Posts: 581

Re: Using dataset from a permanent library in proc sql

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

Occasional Contributor
Posts: 9

Re: Using dataset from a permanent library in proc sql

Here is the log:

 

Capture.PNG

PROC Star
Posts: 581

Re: Using dataset from a permanent library in proc sql

[ Edited ]

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

Solution
a week ago
Super User
Posts: 8,301

Re: Using dataset from a permanent library in proc sql

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 581

Re: Using dataset from a permanent library in proc sql

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 123 views
  • 4 likes
  • 3 in conversation