Operator
|
Example
|
---|---|
comparison operators
|
where quarter = '1998Q1'; where date_id < '03JUL2000'd; where quarter in ('1998Q2','1998Q3'), |
comparison operators
with NOT
|
where quarter ne '1999Q1'; where quarter not in ('1999Q1','1999Q4'), |
comparison operators
with the colon modifier
You can add a colon
modifier (:) to any comparison operator to compare only a specified
prefix of a character string.
The colon modifier cannot
be used with PROC SQL; use the LIKE operator instead.
|
where quarter =: '1998'; |
CONTAINS operator
|
where quarter contains 'Q4'; |
fully bounded range
conditions that specify both an upper and lower limit, which includes
the BETWEEN-AND operator
|
where '01Jan1999'd < date_id < '31Dec1999'd; where date_id between '01Jan1999'd and '31Dec1999'd |
pattern-matching operator
LIKE
|
where quarter like '%Q%'; |
IS NULL or IS MISSING
operator
|
where quarter is null; where quarter is missing; |
Function
|
Example
|
---|---|
TRIM function
|
where trim(region) = 'Queensland'; |
SUBSTR function in the
form of
WHERE SUBSTR
(variable,position,length)='string';
|
where substr(quarter,1,4) = '1998'; |
Element in WHERE Condition
|
Example
|
---|---|
any function other than
TRIM or SUBSTR
|
where weekday(date_id)=2; |
a SUBSTR function that
searches a string beginning at any position after the first
|
where substr(quarter,6,1)='1'; |
the sounds-like operator
(
=* )
|
where quarter=*'1900Q0'; |
arithmetic operators
|
where quantity=quantity+1; |
a variable-to-variable
condition
|
where quantity gt threshold; |
where lastname eq 'Smith' and frstname eq 'John';
where frstname eq 'John' and lastname eq 'Smith';
where frstname eq 'John' or
lastname eq 'Smith';