Advanced SAS

[ttp]

data work.one;
input name$ years;
cards;
Joyce 9
John 4
John 2
jane 6
Tomas 8
;
data work.two;
input name$ years;
cards;
Joyce 31
John 42
Robert 25
Bob 22
Jeff 42
;
proc sql;
select Avg(years)
label="Average of Employment"
from work.one
where name in
(select *
from work.one
except corr
select *
from work.two);
quit;

what will be the output?

A. Average of Employment 5

B.Average of Employment 7

C Average of Employment 6

D. ERROR: Subquery evaluated to more than one row

[VDD]

A subquery cannot select more than one column.

D.

[VDD]

D says rows but the issue is columns.
D for the error.

[bobpep212]

Yeah - what they said. Start at the inner subquery. You are sort of creating a two column table right there. The where statement is looking for name from a list of names, but you haven't specified which of those two columns has name. Even if the select * from subquery has a column called name, it isn't going to assume it contains the same "name" data that's in your outer query.

Good luck on the exam. I'm studying for it too.  

[ttp]

yes very well explained. 

If this question is given what Option do  you choose.

D or B.

My guess is D the closest I can think of. 

yes I am preparing for the exam as well. Good luck to you as well.

[VDD]

as @bobpep212 says change the second * to name and the answer is 7 for B but the code Errors. as is.

data work.one;
input name$ years;
cards;
Joyce 9
John 4
John 2
jane 6
Tomas 8
;
data work.two;
input name$ years;
cards;
Joyce 31
John 42
Robert 25
Bob 22
Jeff 42
;
proc sql;
select Avg(years)
label="Average of Employment"
from work.one
where name in
(select *
from work.one
except corr
select name from work.two);
quit;