Chapter Contents

Previous

Next

sql-expression

operand operator operand

• operand is one of the following:
  • constant is a number or a quoted character string (or other special notation) that indicates a fixed value. Constants are also called literals. Constants are described in SAS Language Reference: Dictionary.

  • column-name is described in column-name .

  • SAS-function is almost any SAS function. Functions are described in SAS Language Reference: Dictionary.

  • The ANSI SQL function COALESCE is supported.

  • summary-function is described in summary-function .

  • query-expression is described in query-expression .

  • USER is a literal that references the userid of the person who submitted the program. The userid that is returned is operating environment-dependent, but PROC SQL uses the same value that the &SYSJOBID macro variable has on the operating environment.

• operator is described in Operators and the Order of Evaluation .

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.

COALESCE Function

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:

   3>null
  -3>null
   0>null

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

Group 6		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');

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');

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');

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

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
 

Subqueries and Efficiency

• Use the MAX function in a subquery instead of the ALL keyword before the subquery. For example, the following queries produce the same result, but the second query is more efficient:

   proc sql;
      select * from proclib.payroll
      where salary> all(select salary
                        from proclib.payroll
                        where jobcode='ME3');
  
   proc sql;
      select * from proclib.payroll
      where salary> (select max(salary)
                     from proclib.payroll
                     where jobcode='ME3');

• With subqueries, use IN instead of EXISTS when possible. For example, the following queries produce the same result, but the second query is more efficient:

  proc sql;
     select *
        from proclib.payroll p
        where exists (select *
                      from staff s
                      where p.idnum=s.idnum
                         and state='CT');
  
  proc sql;
     select *
        from proclib.payroll
        where idnum in (select idnum
                           from staff
                           where state='CT');

Chapter Contents

Previous

Next

Top of Page

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