Using the ANY and ALL Operators in Proc SQL
The ANY and ALL operators are Proc SQL operators which can be used to compare the result of a query expression against either ANY or ALL of the results returned from another.
The following example demonstrates its use as part of a subquery comparing the annual sales of sports shoes from our 'Mozart' company with the top brands nationwide. The ANY operator is used initially to determine which of our company brands are outselling any one or more of the market leaders.
data mozart_shoes; format id z3. name $char24.; input id name $ & sales; datalines; 001 Super-tread trainer 50000 002 X-Pro sports trainer 60000 003 Road runner deluxe 55000 004 Triathlon special 30000 run;
data top_brands; format name $char24.; input name $ & sales; datalines; Cougar 500 58000 Mike running pro 56000 Abibas Super-tread 53000 run;
proc sql; select name, sales from mozart_shoes where sales gt any (select sales from top_brands); quit;
This query returns the following results as expected where these two sports shoes have annual sales greater than at least one of the top brands.
name sales __________________________________ X-Pro sports trainer 60000 Road runner deluxe 55000
Changing the operator to all shows us the one shoe out competing all of the top brands.
name sales __________________________________ X-Pro sports trainer 60000
The NOT operator can also be used to good effect as part of the condition. For example, changing our code to the following:
proc sql; select name, sales from mozart_shoes where sales not gt any (select sales from top_brands); quit;
returns all of the shoes whose annual sales did not exceed any of the top brands.
name sales __________________________________ Super-tread trainer 50000