BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
David_S
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

4 REPLIES 4
Haikuo
Onyx | Level 15

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
Fluorite | Level 6

Hi Hai,

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

David

art297
Opal | Level 21

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
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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