PROC SQL operators

richard_hu2003 — Thu, 14 Apr 2011 16:27:09 GMT

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);
C.
Replace the equal sign with the IN operator.
D.
Qualify the column names with the table names.
Answer:(C)

I am wondering why (B) is not correct. Any thoughts? Thanks a lot.

Re: PROC SQL operators

RickM — Thu, 14 Apr 2011 16:39:13 GMT

Because =ANY(...) is not a valid SQL statement in SAS.

Re: PROC SQL operators

DBailey — Thu, 14 Apr 2011 18:18:50 GMT

presuming newemployee exists and you can match on the name column.

where name in (select name
from newemployee
where salary > 40000)

topic Re: PROC SQL operators in SAS Procedures

PROC SQL operators

Re: PROC SQL operators

Re: PROC SQL operators