| SAS/ACCESS Interface to SYSTEM 2000 Data Management Software: Reference |
Use a SYSTEM 2000 where-clause to
select particular logical
entries from a SYSTEM 2000 database. You may reference any item included in
the access descriptor on which the view descriptor is based, as long as the
password you are using has where-clause authority for each referenced item.
When you include a SYSTEM 2000 where-clause in a view
descriptor, the selection criteria are executed each time you use the view
descriptor in a SAS program. When a SYSTEM 2000 where-clause is invoked, the
interface view engine
- replaces references to SAS variable names with
database item component numbers. (The SAS variable names must correspond to
a database item included in the view descriptor.)
- translates keywords to uppercase for compatibility
with SYSTEM 2000 software.
- expands connecting strings to connect the SAS
WHERE clause to the view where-clause.
- preserves significant blanks in delimited textual
values.
The syntax of the where-clause can include one or more
of the following conditions. Examples of these conditions are presented in Examples.
Note: This is a
partial description of the SYSTEM 2000
where-clause. For a complete description, see the SYSTEM 2000 QUEST
Language manual. However, you cannot include a Collect File item name
or the SAME operator in a where-clause included in a view descriptor. ![[cautionend]](../common/images/cautend.gif)
- WHERE
- is the keyword designating a where-clause.
You can also use the abbreviation WH. The keyword is optional if the where-clause
is the first clause or if you do not specify an ordering-clause.
- expression
- consists of one of the following:
| |
condition |
| |(expression) |
| |NOT
expression |
| |expression AND
expression |
| |expression OR
expression |
| |record HAS
expression |
| |expression AT
n |
- condition [NON-KEY]
item
-
| |unaryoperator |
| |binaryoperator
value |
| |ternaryoperator value * value |
| | CONTAINS
text |
| |* binaryoperator
item* |
- NON-KEY
- allows you to change a key condition to
a non-key one. This capability is not available in a SAS WHERE clause. See Using HAS, AT, and NON-KEY for information
on using connecting strings to extend the function of the NON-KEY specification
to the SAS WHERE clause conditions.
You can abbreviate NON-KEY to NK.
- NOT
- finds the complement of specified criteria.
You can also use the ¬ symbol.
- AND
- combines two expressions by finding data
records that satisfy both expressions. You can also use the & symbol.
- OR
- combines two expressions by finding data
records that satisfy either expression or both. You can also use the | symbol.
- record
- is a schema record name or component number.
- HAS
- specifies a data record by its position
under its parent. This capability is not available in a SAS WHERE clause.
See Using HAS, AT, and NON-KEY
for information on using connecting strings to extend the function of the
AT operator to the SAS WHERE clause conditions.
- n
- is 0 or a positive integer indicating position
of a record under its parent. Zero means the last position.
- item
- is a schema item name or component number
included in the access descriptor. Or you can specify a SAS variable name
if the item is included in the view descriptor. The item can be key or non-key.
- unary-operator: EXISTS or FAILS
- specifies the existence or nonexistence
of values. You can also specify EXIST or EXISTING and FAIL or FAILING.
- binary-operator: EQ, NE, GE, GT, LE, or LT
- compares an item with a value or compares
two items. You can also use these symbols:
| Operator |
Alternate Form |
| EQ |
= |
| NE |
¬= or != |
| GE |
>= or => or ¬< or !< |
| GT |
> |
| LE |
<= or =< or ¬> or !> |
| LT |
< |
- ternary-operator: EQ, NE, or
SPANS
- compares an item with a range of values.
Ternary operators require a low value and a high value. You can also specify
SPAN or SPANNING, and you can use these symbols:
| Operator |
Alternate Form |
| EQ |
= |
| NE |
¬= or
!= |
- value
- is a literal value or the SYSTEM 2000 system
string *TODAY*. Optionally, you can enclose a value with a delimiter of your
choice. Sometimes you may need delimiters around character values, for example,
to preserve a mixed case value. Any special character that appears at the
beginning and end of a character value is assumed to be a delimiter. Consider
these examples:
where c1 = 'Abc De' looks for Abc De
where c1 = @Abc De@ looks for Abc De
where c1 = @Abc De looks for @Abc De
- CONTAINS
- searches for characters within an item's
values.You can also specify CONT, CONTAIN, or CONTAINING.
- text
- For the syntax and explanation of CONTAINS
text, see SYSTEM 2000 QUEST Language.
This section gives examples using different forms of
the SYSTEM 2000 where-clause.
Unary operators
Unary operators search for values that
exist or do not exist using the EXISTS and FAILS operators. The following
where-clause qualifies data records having a value for the item ACCRUED VACATION.
where accrued vacation exists
The following where-clause qualifies data records not
having a value for the item ACCRUED VACATION, that is, null items.
where accrued vacation fails
Note that SYSTEM 2000 unary operators are similar to
SAS missing values expressions.
Binary operators
Binary operators compare items with a
value or compare two items using the EQ, NE, GT, GE, LT, or LE operators (or
their equivalent symbols). The following where-clause qualifies data records
having the value for EMPLOYEE NUMBER equal to 1224.
where employee number=1224
The next where-clause qualifies data records where EMPLOYEE
STATUS is not equal to FULL TIME. (It does not, however, qualify those records
where EMPLOYEE STATUS is null as FAILS would.)
where employee status ne full time
The next where-clause qualifies data records where the
value for HIRE DATE is greater than or equal to June 1, 1987.
where hire date=>06/01/1987
The next where-clause qualifies data records where the
value for C105 equals the value for C4.
where C4 * EQ C105 *
Ternary operators
Ternary operators search for values
in a range of values using the SPANS, EQ, and NE operators (or their equivalent
symbols). The following where-clause qualifies data records where BIRTHDAY
spans the dates January 1, l949 and January 31, 1949, inclusively.
wh birthday spans 01/01/1949 * 01/31/1949
CONTAINS operator
The CONTAINS operator searches for values
that contain patterns of characters within values. The item must be a CHARACTER,
TEXT, or UNDEFINED item. For example, the following where-clause qualifies
data records where the values for STREET ADDRESS contain the character string
RIM ROCK.
wh street address contains /RIM ROCK/
Combining conditions with AND and OR
Using the AND and OR operators, you can combine
two or more conditions. AND combines two conditions by selecting values that
satisfy both conditions, and OR combines two conditions by selecting values
that satisfy either or both conditions. For example, the following where-clause
qualifies data records having COBOL in the item SKILL TYPE and 4 in the item
YEARS OF EXPERIENCE.
where skill type=cobol & years of experience=4
Not qualifying a condition with NOT
Using the NOT operator, you can select data
records where values do not match a condition. For example, the following
where-clause selects data records for the item PAY SCHEDULE that do not equal
the value HOURLY or that are null.
wh ¬pay schedule=hourly
Designating specific types of records with HAS
Using the HAS operator, you can specify a
focal record. For example in the following where-clause, the HAS operators
specify C0 (the ENTRY record) as the focal record, because both conditions
refer to the same schema record (C201). In this case, the HAS operators qualify
C0 records that have the values COBOL and FORTRAN for C201. (If the HAS operator
were not used, no records would qualify, because there would never be a C201
value of both COBOL and FORTRAN.)
wh C0 has c201 eq cobol and C0 has c201 eq fortran
Specifying position with AT
Using the AT operator, you can select values
that are stored in a specified position in the database. Values must satisfy
the condition and occupy a specific position. A data record's position is
its number in a left-to-right enumeration below its parent record. For example,
the following where-clause qualifies the data record in position 2 in a logical
entry.
wh position title eq programmer at 2
Processing order
The order in which SYSTEM 2000 software
processes conditions can affect which data records are selected. The software
processes conditions with operators in this order: AT, HAS, NOT, AND, and
OR.
When conditions are joined by the same operator, SYSTEM
2000 software first processes key conditions (ones that are indexed) from
right to left, then non-key conditions (ones not indexed) from right to left.
You can alter processing order by changing the order
of the conditions and by using parentheses around conditions. The software
processes conditions enclosed in parentheses first.
For example, because the software processes the AND
operator prior to the OR operator, to access those employees with an MBA degree
and either a major or minor in Marketing, the following where-clause would
yield the desired results:
wh degree=mba &
(major field=marketing|minor field=marketing)
On the other hand, if you use the following where-clause,
SYSTEM 2000 software would also select those employees who have a minor in
Marketing and degrees other than MBAs.
wh degree=mba &
major field=marketing|minor field=marketing
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.
