Chapter Contents


The SQL Procedure


Produces a value from a sequence of operands and operators.

operand operator operand

Note:   SAS functions, including summary functions, can stand alone as SQL expressions. For example

select min(x) from table;

select scan(y,4) from table;

SAS Functions
PROC SQL supports the same SAS functions as the DATA step, except for the functions LAG, DIF, and SOUND. For example, the SCAN function is used in the following query:

    select style, scan(street,1) format=$15.
       from houses;

See SAS Language Reference: Dictionary for complete documentation on SAS functions. Summary functions are also SAS functions. See summary-function for more information.

PROC SQL also supports the ANSI SQL function COALESCE. COALESCE accepts multiple column names of the same data type. The COALESCE function returns the first argument whose value is not a SAS missing value. In some SQL DBMSs, the COALESCE function is called the IFNULL function. See PROC SQL and the ANSI Standard for more information.

For an example that uses COALESCE, see Performing an Outer Join .

USER Literal
USER can be specified in a view definition, for example, to create a view that restricts access to those in the user's department:

   create view myemp as
      select * from dept12.employees
         where manager=user;

This view produces a different set of employee information for each manager who references it.

Operators and the Order of Evaluation
The order in which operations are evaluated is the same as in the DATA step with this one exception: NOT is grouped with the logical operators AND and OR in PROC SQL; in the DATA step, NOT is grouped with the unary plus and minus signs.

Unlike missing values in some versions of SQL, missing values in the SAS System always appear first in the collating sequence. Therefore, in Boolean and comparison operations, the following expressions resolve to true in a predicate:


You can use parentheses to group values or to nest mathematical expressions. Parentheses make expressions easier to read and can also be used to change the order of evaluation of the operators. Evaluating expressions with parentheses begins at the deepest level of parentheses and moves outward. For example, SAS evaluates A+B*C as A+(B*C), although you can add parentheses to make it evaluate as (A+B)*C for a different result.

Higher priority operations are performed first: that is, group 0 operators are evaluated before group 5 operators. Operators and Order of Evaluation shows the operators and their order of evaluation, including their priority groups.

Operators and Order of Evaluation
Operator Description
0 ( ) forces the expression enclosed to be evaluated first
1 case-expression selects result values that satisfy specified conditions
2 ** raises to a power

unary +, unary - indicates a positive or negative number
3 * multiplies

/ divides
4 + adds

- subtracts
5 || concatenates
6 <NOT> BETWEEN condition See BETWEEN condition .

<NOT> CONTAINS condition see CONTAINS condition .

<NOT> EXISTS condition See EXISTS condition .

<NOT> IN condition See IN condition .

IS <NOT> condition See IS condition .

<NOT> LIKE condition See LIKE condition .
7 =, eq equals

¬=, ^=, < >, ne does not equal

>, gt is greater than

<, lt is less than

>=, ge is greater than or equal to

<=, le is less than or equal to

=* sounds like (use with character operands only). See Retrieving Values with the SOUNDS-LIKE Operator .
8 &, AND indicates logical AND
9 |, OR indicates logical OR
10 ¬, ^, NOT indicates logical NOT

Symbols for operators may vary, depending on the operating environment. See SAS Language Reference: Dictionary for more information on operators and expressions.

Query Expressions (Subqueries)
Query-expressions are called subqueries when used in WHERE or HAVING clauses. A subquery is a query-expression that is nested as part of another query-expression. A subquery selects one or more rows from a table based on values in another table.

Depending on the clause that contains it, a subquery can return a single value or multiple values. If more than one subquery is used in a query-expression, the innermost query is evaluated first, then the next innermost query, and so on, moving outward.

PROC SQL allows a subquery (contained in parentheses) at any point in an expression where a simple column value or constant can be used. In this case, a subquery must return a single value, that is, one row with only one column. When a subquery returns one value, you can name the value with a column alias and refer to it by that name elsewhere in the query. This is useful for replacing values with other values returned using a subquery.

The following is an example of a subquery that returns one value. This PROC SQL step subsets the PROCLIB.PAYROLL table based on information in the PROCLIB.STAFF table. (PROCLIB.PAYROLL is shown in Creating a Table from a Query's Result , and PROCLIB.STAFF is shown in Joining Two Tables .) PROCLIB.PAYROLL contains employee identification numbers (IdNumber) and their salaries (Salary) but does not contain their names. If you want to return only the row from PROCLIB.PAYROLL for one employee, you can use a subquery that queries the PROCLIB.STAFF table, which contains the employees' identification numbers and their names (Lname and Fname).

options ls=64 nodate nonumber;
proc sql;
    title 'Information for Earl Bowden';
    select *
       from proclib.payroll
       where idnumber=
             (select idnum
             from proclib.staff
             where upcase(lname)='BOWDEN');
[HTML Output]  [Listing Output] Subqueries can return multiple values. The following example uses the tables PROCLIB.DELAY and PROCLIB.MARCH. These tables contain information about the same flights and have the Flight column in common. The following subquery returns all the values for Flight in PROCLIB.DELAY for international flights. The values from the subquery complete the WHERE clause in the outer query. Thus, when the outer query is executed, only the international flights from PROCLIB.MARCH are in the output.
options ls=64 nodate nonumber;
proc sql outobs=5;
   title 'International Flights from';
   title2 'PROCLIB.MARCH';
   select Flight, Date, Dest, Boarded
      from proclib.march
      where flight in 
            (select flight
             from proclib.delay
             where destype='International');
[HTML Output]  [Listing Output] Sometimes it is helpful to compare a value with a set of values returned by a subquery. The keywords ANY or ALL can be specified before a subquery when the subquery is the right-hand operand of a comparison. If ALL is specified, the comparison is true only if it is true for all values returned by the subquery. If a subquery returns no rows, the result of an ALL comparison is true for each row of the outer query.

If ANY is specified, the comparison is true if it is true for any one of the values returned by the subquery. If a subquery returns no rows, the result of an ANY comparison is false for each row of the outer query.

The following example selects all those in PROCLIB.PAYROLL who earn more than the highest paid ME3:

options ls=64 nodate nonumber ;
proc sql;
title "Employees who Earn More than";
title2 "All ME's";
   select *
     from proclib.payroll
     where salary > all (select salary
                         from proclib.payroll
                         where jobcode='ME3');
[HTML Output]  [Listing Output]

Note:   See the first item in Subqueries and Efficiency for a note about efficiency when using ALL.  [cautionend]

Correlated Subqueries
In a correlated subquery, the WHERE expression in a subquery refers to values in a table in the outer query. The correlated subquery is evaluated for each row in the outer query. With correlated subqueries, PROC SQL executes the subquery and the outer query together.

The following example uses the PROCLIB.DELAY and PROCLIB.MARCH tables. A DATA step creates PROCLIB.DELAY. PROCLIB.MARCH is shown in Producing All the Possible Combinations of the Values in a Column . PROCLIB.DELAY has the Flight, Date, Orig, and Dest columns in common with PROCLIB.MARCH:

proc sql outobs=5;
   title 'International Flights';
   select *
      from proclib.march
      where 'International' in 
           (select destype
            from proclib.delay
            where march.Flight=delay.Flight);

The subquery resolves by substituting every value for MARCH.Flight into the subquery's WHERE clause, one row at a time. For example, when MARCH.Flight= 219, the subquery resolves as follows:

  1. PROC SQL retrieves all the rows from DELAY where Flight= 219 and passes their DESTYPE values to the WHERE clause.

  2. PROC SQL uses the DESTYPE values to complete the WHERE clause:
    where 'International' in
       ('International','International', ...)

  3. The WHERE clause checks to see if International is in the list. Because it is, all rows from MARCH that have a value of 219 for Flight become part of the output.

International Flights for March contains the rows from MARCH for international flights only.

International Flights for March
[HTML Output]  [Listing Output]

Subqueries and Efficiency

Chapter Contents



Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.