Proc SQL - Two tables have the same name, but are in different libraries

deleted_user — Wed, 28 May 2008 21:05:12 GMT

I have a table named "salary" in a library named "lib1" and another table named "salary" in a library named "lib2". "Salary" in "lib1" contains the columns "id" and "payrate". "Salary" in "lib2" also contains the columns "id" and "payrate". The values in the columns of the "salary" table in "lib1" are not identical to the values in the columns of the "salary" table in "lib2".

In the 'select' clause of the code below, it is not specified which "salary" table is being referred to in "salary.id" and "salary.payrate". Also, in the 'where' clause, it is not specified which "salary" tables are being referred to in "salary.id=salary.id".

proc sql;
select salary.id, salary.payrate
from lib1.salary, lib2.salary
where salary.id=salary.id;
quit;

Does anyone know how I can specify that the "salary.id" in the select clause comes from "salary" in the "lib1" library and "salary.payrate" comes from "salary" in the "lib2" library? Also, how do I specify that the first "salary.id" in the 'where' clause specifies "salary.id" in "lib1" and the second "salary.id" specifies "salary.id" in "lib2"?

I tried the following code, but it didn't work and I got error messages:

proc sql;
select lib1.salary.id, lib2.salary.payrate
from lib1.salary, lib2.salary
where lib1.salary.id=lib2.salary.id;
quit;

I could rename the tables so they have different names, but I am curious to know if it is possible for the sql to work with the tables having the same name.

Thanks.

Re: Proc SQL - Two tables have the same name, but are in different libraries

Cynthia_sas — Wed, 28 May 2008 21:37:55 GMT

Hi:
This may be the place where the use of AS in the FROM clause might help you out:
[pre]
libname perm 'c:\temp';
data perm.class;
set sashelp.class;
if age ge 14;
run;

proc sql;
select a.name, b.age, b.height
from perm.class as a, sashelp.class as b
where a.name=b.name;
quit;

[/pre]

cynthia

Re: Proc SQL - Two tables have the same name, but are in different libraries

deleted_user — Thu, 29 May 2008 13:44:16 GMT

Thanks, Cynthia!

topic Re: Proc SQL - Two tables have the same name, but are in different libraries in SAS Procedures

Proc SQL - Two tables have the same name, but are in different libraries

Re: Proc SQL - Two tables have the same name, but are in different libraries

Re: Proc SQL - Two tables have the same name, but are in different libraries