Type of Operator
|
Example
|
---|---|
comparison
|
where membertype='GOLD' |
logical
|
where visits<=3 or status='new' |
concatenation
|
where name=trim(last) ||', '||first |
proc sql;
select ffid, name, state, pointsused
from sasuser.frequentflyers
where membertype='GOLD' and pointsused>0
order by pointsused;
Conditional Operator
|
Tests for ...
|
Example
|
---|---|---|
BETWEEN-AND
|
values that occur within
an inclusive range
|
where salary between 70000 and 80000 |
CONTAINS or ?
|
values that contain
a specified string
|
where name contains 'ER' where name ? 'ER' |
IN
|
values that match one
of a list of values
|
where code in ('PT' , 'NA', 'FA') |
IS MISSING or IS NULL
|
missing values
|
where dateofbirth is missing where dateofbirth is null |
LIKE (with %, _)
|
values that match a
specified pattern
|
where address like '% P%PLACE' |
=*
|
values that sound
like a specified value
|
where lastname=* 'Smith' |
ANY
|
values that meet a specified
condition with respect to any one of
the values returned by a subquery
|
where dateofbirth < any
(select dateofbirth
from sasuser.payrollmaster
where jobcode='FA3') |
ALL
|
values that meet a specified
condition with respect to all the
values returned by a subquery
|
where dateofbirth < all
(select dateofbirth
from sasuser.payrollmaster
where jobcode='FA3') |
EXISTS
|
the existence of values
returned by a subquery
|
where exists
(select *
from sasuser.flightschedule
where fa.empid=
flightschedule.empid) |
General form, BETWEEN-AND
operator:
BETWEEN value-1 AND value-2
value-1
is the value at the
one end of the range
value-2
is the value at the
other end of the range.
|
Example
|
Returns rows in which...
|
---|---|
where date between '01mar2000'd and '07mar2000'd In this example, the
values are specified as date constants.
|
the value of Date is
01mar2000 , 07mar2000 ,
or any date value in between
|
where salary between 70000 and 80000 |
the value of Salary
is 70000, 80000, or any numeric value in between
|
where salary not between 70000 and 80000 |
the value of Salary
is not between or equal to 70000 and 80000
|
General form, CONTAINS
operator:
sql-expression CONTAINS sql-expression
sql-expression ? sql-expression
sql-expression
is a character column,
string (character constant), or expression. A string is a sequence
of characters to be matched that must be enclosed in quotation marks.
|
General form, IN operator:
column IN (constant-1<,...constant-n>)
column
specifies the selected
column name
constant-1 and constant-n
represent a list that
contains one or more specific values. The list of values must be enclosed
in parentheses and separated by either commas or spaces. Values can
be either numeric or character. Character values must be enclosed
in quotation marks.
|
General form, IS MISSING
or IS NULL operator:
column IS
MISSING
column IS
NULL
column
specifies the selected
column name.
|
proc sql;
select boarded, transferred,
nonrevenue, deplaned
from sasuser.marchflights
where boarded is missing;
where boarded = . where flight = ' 'However, the advantage of using the IS MISSING or IS NULL operator is that you do not have to specify the data type (character or numeric) of the column.
General form, LIKE operator:
column LIKE 'pattern'
column
specifies the column
name
pattern
specifies the pattern
to be matched and contains one or both of the special characters underscore
( _ ) and percent
sign (%). The entire pattern
must be enclosed in quotation marks and matching is case sensitive.
|
Special Character
|
Represents
|
---|---|
underscore ( _ )
|
any single character
|
percent sign (%)
|
any sequence of zero
or more characters
|
proc sql;
select ffid, name, address
from sasuser.frequentflyers
where address like '% P%PLACE';
General form, sounds-like
(=*) operator:
sql-expression =* sql-expression
sql-expression
is a character column,
string (character constant), or expression. A string is a sequence
of characters to be matched that must be enclosed in quotation marks.
|
where lastname =* 'Smith';