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.