Using alias to select column from another dataset

[David_S]

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

[Haikuo · Accepted solution]

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;

[David_S]

Hi Hai,

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

David

[art297]

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;

[Haikuo]

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