sas adv 63 question: can anybody help me to explain how to answer? Thanks

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

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  

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：question anybody explain Answer thanks following question anybody average include

这题应该选C吗？感觉C不对。

如果答案C选错的话请忽略我一下回答。
A 最正常的coding了，group by算每组的和。
B 最***coding, 是把一个表当成两个表，要问为啥这样，我觉得还是因为C不可以 C in line里已经选择了这个Data set,括号外面又是这个data set,一般这样的code都是错的 （个人经验，欢迎敲转）所以要写成B这种***的code
C 错，原因见上。
D 括号select里面相当于产生了一个dataset,和work pilots 并列，所以没问题。

说错了别打我

crackman said it was C. I would like to read more about it. Which chapter I should read for this question
Thanks

you should read summarizing and grouping data under proc sql programming.
a)as text states in the book, if a select clause contains summary functions and additional columns outside of summary functions, calculates a single value for the entire table or, if groups are specified, for each group, and displays all rows of output with the single or grouped value(s) repeated. Hence, A) can generate the printed result.

b)here is what i understand: SAS reads jobcode,salary from  P2(work.pilots) into PDV, then it comes to (select avg(salary) ) part, it will read back the jobcode value which is already read in PDV to identify the corresponding avg(salary)  value because p1.jobcode=p2.jobcode is presented. although it can generate the same result, it requires more resource, because it calculates the avg every time;

c)similar to B), but it is missing the where statement.
d)it is like divided into two tables, one is work.pilots, another one has only one column, the average values based on jobcode, and then uses join.

maybe i don't get it all right, but hopefully it can give you a hint.

cheers
Jack

Thank you so much, Jack