|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
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.
||expression AND expression|
||expression OR expression|
||record HAS expression|
||expression AT n|
||ternaryoperator value * value|
|| CONTAINS text|
||* binaryoperator item*|
You can abbreviate NON-KEY to NK.
|NE||¬= or !=|
|GE||>= or => or ¬< or !<|
|LE||<= or =< or ¬> or !>|
|NE||¬= or !=|
where c1 = 'Abc De' looks for Abc De where c1 = @Abc De@ looks for Abc De where c1 = @Abc De looks for @Abc De
This section gives examples using different forms of
the SYSTEM 2000 where-clause.
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 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 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
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/
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
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
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
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
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
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.