Help using Base SAS procedures

Using alias to select column from another dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Using alias to select column from another dataset

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


Accepted Solutions
Solution
‎02-22-2015 06:49 PM
Respected Advisor
Posts: 3,156

Re: Using alias to select column from another dataset

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;

View solution in original post


All Replies
Solution
‎02-22-2015 06:49 PM
Respected Advisor
Posts: 3,156

Re: Using alias to select column from another dataset

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;

Occasional Contributor
Posts: 16

Re: Using alias to select column from another dataset

Hi Hai,

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

David

PROC Star
Posts: 7,467

Re: Using alias to select column from another dataset

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;

Respected Advisor
Posts: 3,156

Re: Using alias to select column from another dataset

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 470 views
  • 3 likes
  • 3 in conversation