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.
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.
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
Here is the log:
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
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.