topic Re: Advanced SAS in SAS Programming

Advanced SAS

ttp — Sat, 15 Sep 2018 00:15:01 GMT

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

Re: Advanced SAS

VDD — Sat, 15 Sep 2018 00:18:17 GMT

A subquery cannot select more than one column.

Re: Advanced SAS

VDD — Sat, 15 Sep 2018 00:20:34 GMT

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

Re: Advanced SAS

bobpep212 — Sat, 15 Sep 2018 00:34:46 GMT

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.

Re: Advanced SAS

VDD — Mon, 17 Sep 2018 01:02:59 GMT

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;

Re: Advanced SAS

ttp — Mon, 17 Sep 2018 00:53:02 GMT

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.