SAS advance exam question 2

shiyiming

The SAS data set WORK.CHECK has an index on the variable Code and the following SAS program is submitted.

proc sort data=WORK.CHECK;
     by Code;
run;

Which describes the result of submitting the SAS program?
A. The index on Code is deleted.
B. The index on Code is updated.
C. The index on Code is uneffected.
D. The sort does not execute.

D is answer? Why?

shiyiming

A question about Proc SQL.

The table WORK.PILOTS contains the following data:
WORK.PILOTS
Id Name Jobcode Salary
--- ------ ------- ------
001 Albert PT1 50000
002 Brenda PT1 70000
003 Carl PT1 60000
004 Donna PT2 80000
005 Edward PT2 90000
006 Flora PT3 100000

The data set was summarized to include average salary based on jobcode:
Jobcode Salary Avg
------- ------ -----
PT1 50000 60000
PT1 70000 60000
PT1 60000 60000
PT2 80000 85000
PT2 90000 85000
PT3 100000 100000

Which SQL statement could NOT generate this result?
A.
select
        Jobcode,
        Salary,
        avg(Salary) label='Avg'
from WORK.PILOTS
group by Jobcode
order by Id
;

B.
select
        Jobcode,
        Salary,
        (select avg(Salary)
                   from WORK.PILOTS as P1
                    where P1.Jobcode=P2.Jobcode) as Avg
from WORK.PILOTS as P2
order by Id
;

C.
select
        Jobcode,
         Salary,
         (select avg(Salary)
                    from WORK.PILOTS
                    group by Jobcode) as Avg
from WORK.PILOTS
order by Id
;

D.
select Jobcode,
          Salary,
         Avg
from WORK.PILOTS,
        (select Jobcode as Jc,
                   avg(Salary) as Avg
          from WORK.PILOTS
          group by 1)
where Jobcode=Jc
order by Id
;

My question is which way is the best way to get the output, and why C is wrong?

shiyiming

1、我选D，因为数据集已经对code变量建立简单索引了。其实也好理解，本来费半天劲建了个索引，真要排了序，这索引岂不是报废要重建了吗。
2、我选C。select avg(Salary) from WORK.PILOTS group by Jobcode子句将返回多个值，用哪个不用哪个呀。A中select子句的salary变量没出现在group by子句中，将使汇总数据与详细数据拼合在一起；B是个Correlated Subquerie；D是个In-Line View。不管效率高低，只看喜好的话，我首选A，写着简单；B和D我更倾向于D，把汇总数据准备好再inner join感觉好安心，跟B不熟，提心吊胆的怕被绕进去。

shiyiming

Thanks .