BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mahip
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

5 REPLIES 5
novinosrin
Tourmaline | Level 20

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
Obsidian | Level 7

Here is the log:

 

Capture.PNG

novinosrin
Tourmaline | Level 20

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
Super User

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
Tourmaline | Level 20

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;

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