Amadeus cookies policy - you'll see this message only once.

Amadeus use cookies on this website. They help us to know a little bit about you and how you use our website, which improves the browsing experience and marketing - both for you and for others. They are stored locally on your computer or mobile device. To accept cookies, continue browsing as normal. Or, go to the privacy policy for more information.

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