|The SQL Procedure|
|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;
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.
For an example that uses COALESCE, see Performing an Outer Join .
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|
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.
|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|
|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 .|
|¬=, ^=, < >, 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)|
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');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');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
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.
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:
219and passes their DESTYPE values to the WHERE clause.
where 'International' in ('International','International', ...)
Internationalis in the list. Because it is, all rows from MARCH that have a value of
219for 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|
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');
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');
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.