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

Triathlon special 30000