由SAS adv 63题中
Given the SAS data sets:
WORK.EMPLOYEE WORK.NEWEMPLOYEE
Name Dept Names Salary
Alan Sales Michelle 50000
Michelle Sales Paresh 60000
A SAS program is submitted and the following is written to the SAS log:
101 proc sql;
102 select dept, name
103 from WORK.EMPLOYEE
104 where name=(select names
from newemployee
where salary > 40000)
ERROR: Subquery evaluated to more than one row.
105 ;
106 quit;
What would allow the program to successfully execute without errors?
A. Replace the where clause with:
where EMPLOYEE.Name=(select Names delimited with‘,'
from WORK.NEWEMPLOYEE
where Salary > 40000);
B. Replace line 104 with:
where EMPLOYEE.Name =ANY (select Names separated with‘,'
from WORK.NEWEMPLOYEE
where Salary > 40000);
where型子查询:指把内部查询的结果作为外层查询的比较条件。
from型子查询:把内层的查询结果当成临时表,供外层sql再次查询。
in子查询:内层查询语句仅返回一个数据列,这个数据列的值将供外层查询语句进行比较。
exists子查询:把外层的查询结果,拿到内层,看内层是否成立,简单来说后面的返回true,外层(也就是前面的语句)才会执行,否则不执行。
any/some子查询:只要满足内层子查询中的任意一个比较条件,就返回一个结果作为外层查询条件。
all子查询:内层子查询返回的结果需同时满足所有内层查询条件。
比较运算符子查询:子查询中可以使用的比较运算符如 “>” “<” “= ” “!=”
作者:机智的豆子
来源:CSDN
原文:https://blog.csdn.net/qq_39380737/article/details/81127497
版权声明:本文为博主原创文章,转载请附上博文链接!
正确样例
proc sql;
select *
from datalib.dm
where Subject_Id=any(select Subject_Id
from datalib.ds
where dsyn<'是') ;
quit;
替换
where Subject_Id in(select Subject_Id
where Subject_Id=some(select Subject_Id
where exists(select *
from datalib.ds
where dm.Subject_Id=ds.Subject_Id and dsyn<'是') ;
union 纵表连接 表现得不如set
set 中 两表纵表连接
data c;
set a b;
就直接 b接在a后面
data c;
set a b;
by x y;
有by 就是a b 必须先按照 x y 排序
这样pdv读入之前就会对两个数据集中的数据先按照 x y进行比较