SAS中文论坛

标题: 跟crackman做sas adv认证试题（2） [打印本页]
作者: shiyiming    时间: 2010-11-15 23:16
标题: 跟crackman做sas adv认证试题（2）
From crackman's blog on Baidu


               
                <p><font size="4" face="宋体">Given the SAS data sets:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  <br>
<br>
&nbsp;&nbsp;  WORK.EMPLOYEE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  WORK.NEWEMPLOYEE&nbsp;&nbsp;  <br>
<br>
&nbsp;&nbsp;  Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Dept&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Names&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Salary  <br>
&nbsp;&nbsp;  --------&nbsp;&nbsp;  -----&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  --------&nbsp;&nbsp;  ------  <br>
&nbsp;&nbsp;  Alan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Sales&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Michelle&nbsp;&nbsp;  50000&nbsp;&nbsp;  <br>
&nbsp;&nbsp;  Michelle&nbsp;&nbsp;  Sales&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Paresh&nbsp;&nbsp;&nbsp;&nbsp;  60000&nbsp;&nbsp;  <br>
<br>
  A SAS program is submitted and<br>
  the following is written to the SAS log:&nbsp;&nbsp;&nbsp;&nbsp;  <br>
<br>
  101  proc sql;<br>
  102&nbsp;&nbsp;&nbsp;  select dept, name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  <br>
  103&nbsp;&nbsp;&nbsp;  from WORK.EMPLOYEE&nbsp;&nbsp;&nbsp;&nbsp;  <br>
  104&nbsp;&nbsp;&nbsp;  where name=(select names<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  from newemployee&nbsp;&nbsp;&nbsp;  <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  where salary &gt; 40000)&nbsp;&nbsp;&nbsp;&nbsp;  <br>
  ERROR: Subquery evaluated to more than one row. <br>
  105  ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  <br>
  106  quit;  <br>
<br>
  What would allow the program to<br>
  successfully execute without errors?&nbsp;&nbsp;  </font></p>
<p><br>
<font size="4" face="宋体">A.Replace the where clause with:&nbsp;&nbsp;&nbsp;  <br>
<br>
where EMPLOYEE.Name=(select Names delimited with ','&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  from WORK.NEWEMPLOYEE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  where Salary &gt; 40000);&nbsp;&nbsp;&nbsp;&nbsp;  <br>
<br>
B.Replace line 104 with:&nbsp;&nbsp;  <br>
<br>
where EMPLOYEE.Name =ANY (select Names separated with ',' <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  from WORK.NEWEMPLOYEE <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  where Salary &gt; 40000);<br>
C.Replace the equal sign with the IN operator.<br>
<br>
D.Qualify the column names with the table names.&nbsp;&nbsp;&nbsp;&nbsp;  </font></p>
<p><font size="4" face="宋体">答案为C。<br>
本文属于博客：</font><a href="http://hi.baidu.com/crack_man/home"><font size="4" face="宋体"><!-- m --><a class="postlink" href="http://hi.baidu.com/crack_man/home">http://hi.baidu.com/crack_man/home</a><!-- m --></font></a><font size="4" face="宋体"> 版权归作者所有，欢迎转载！如有转载，请务必注明出处！未经本文作者同意不得用于商业应用。<br>
首先分析这个原程序第104行，中where name=(select names from newemployee where salsry&gt;40000)<br>
原程序在执行过程，应先是执行子查询，select names from newemployee where salsry&gt;40000，将返回的结果作为下一个查询的筛选条件。<br>
在这里返回的结果NAMES这个变量包括了两个值Michelle和Paresh，所以在执行过程出现错误。那么应该改成 IN或者ANY。IN ANY指定的是一个匹配范围，而等号必须是一个具体匹配的值。<br>
这里可以思考SEPARATED WITH这个语句，SAS认为是错误。</font></p>
<p> </p> <a href="http://hi.baidu.com/crack%5Fman/blog/item/a524fe077f8ddafc09fa9368.html">阅读全文</a>
               
                <br/><b>类别：</b><a href="http://hi.baidu.com/crack%5Fman/blog/category/%B8%FAcrackman%D7%F6sas%20adv%C8%CF%D6%A4%CA%D4%CC%E2">跟crackman做sas adv认证试题</a>&nbsp;<a href="http://hi.baidu.com/crack%5Fman/blog/item/a524fe077f8ddafc09fa9368.html#comment">查看评论</a>



欢迎光临 SAS中文论坛 (https://mysas.net/forum/) Powered by Discuz! X3.2