topic Re: Using alias to select column from another dataset in SAS Procedures

Using alias to select column from another dataset

David_S — Sun, 22 Feb 2015 21:55:40 GMT

Hello,

How do you appropriately select Employee_Gender (a column that does NOT exist in Donor_list table)? This syntax gives me an ERROR: Unresolved reference to table/correlation name b. However, if I include the Employee_payroll table on original FROM statement I get ERROR: Ambiguous reference, column Employee_ID is in more that one table. What am I missing?

proc sql;

select a.Employee_ID, a.Employee_Name, b.Employee_Gender

from Mydata.Donor_list as a

where Employee_ID in

(select b.Employee_ID

from SQL.Employee_payroll as b

where a.Employee_ID = b.Employee_ID)

quit;

Thanks,

David

Re: Using alias to select column from another dataset

Haikuo — Sun, 22 Feb 2015 23:49:26 GMT

In this case, how about using an inner join instead of a sub-query:

proc sql;

select a.Employee_ID, a.Employee_Name, b.Employee_Gender

from Mydata.Donor_list as a, SQL.Employee_payroll as b

where a.Employee_ID = b.Employee_ID

quit;

Re: Using alias to select column from another dataset

art297 — Mon, 23 Feb 2015 00:16:37 GMT

You'll have to add a semi-colon, just before the quit statement, in order for Haikuo's code to run.

And, dependent upon one's preferences, the following code does the same thing:

proc sql;

select a.Employee_ID, a.Employee_Name, b.Employee_Gender

from Mydata.Donor_list as a

inner join SQL.Employee_payroll as b

on a.Employee_ID = b.Employee_ID

;

quit;

Re: Using alias to select column from another dataset

Haikuo — Mon, 23 Feb 2015 01:35:35 GMT

Thanks, Art. Obviously my compiler wasn't working :smileysilly:

Re: Using alias to select column from another dataset

David_S — Mon, 23 Feb 2015 04:12:09 GMT

Hi Hai,

Don't know why a join didn't cross my mind. That worked. Thank you,

David