proc sql; select jobcode, avg(salary) as AvgSalary format=dollar11.2 from sasuser.payrollmaster group by jobcode having avg(salary) > (select avg(salary) from sasuser.payrollmaster);
proc sql; select empid, lastname, firstname, city, state from sasuser.staffmaster where empid in (select empid from sasuser.payrollmaster where month(dateofbirth)=2);
where dateofbirth < any <subquery...>
where dateofbirth < any <subquery...>
Comparison Operator
with ANY
|
Outer Query Selects...
|
Example
|
---|---|---|
> ANY
|
values that are greater
than any value returned by the subquery
|
If the subquery returns
the values 20, 30, 40, then the outer query selects all values that
are > 20 (the lowest value that was returned by the subquery).
|
< ANY
|
values that are less
than any value returned by the subquery
|
If the subquery returns
the values 20, 30, 40, then the outer query selects all values that
are < 40 (the highest value that was returned by the subquery).
|
= ANY
|
values that are equal
to any value returned by the subquery
|
If the subquery returns
the values 20, 30, 40, the outer query selects all values that are
= 20 or = 30 or = 40.
|
proc sql;
select empid, jobcode, dateofbirth
from sasuser.payrollmaster
where jobcode in ('FA1','FA2')
and dateofbirth < any
(select dateofbirth
from sasuser.payrollmaster
where jobcode='FA3'),
where jobcode in ('FA1','FA2') and dateofbirth < (select max(dateofbirth) from [...]
proc sql;
select empid, jobcode, dateofbirth
from sasuser.payrollmaster
where jobcode in ('FA1','FA2')
and dateofbirth < all
(select dateofbirth
from sasuser.payrollmaster
where jobcode='FA3'),
where jobcode in ('FA1','FA2') and dateofbirth < (select min(dateofbirth) from [...]