|SAS/ACCESS Interface to SYSTEM 2000 Data Management Software: Reference|
In addition to or instead of including a SYSTEM 2000 where-clause in your view descriptor for selection criteria, you can also specify a SAS WHERE clause in a SAS program for selection criteria.
Note: Unlike a SYSTEM 2000 where-clause stored in a
view descriptor, a SAS WHERE clause is restricted to variables corresponding
to items included in the view descriptor. (A SYSTEM 2000 where-clause can
reference items contained in a view descriptor and items contained in the
access descriptor that the view descriptor is based on.)
When you specify a SAS WHERE clause, the SAS/ACCESS interface view engine translates those conditions into SYSTEM 2000 conditions. Then, if the view descriptor includes a SYSTEM 2000 where-clause, the interface view engine connects the conditions with the Boolean operator AND. By default, the SAS WHERE clause conditions are connected to the end of the view descriptor conditions. For example, if a view descriptor includes the condition
and the SAS WHERE clause condition translates into
the resulting selection criteria are
sex=female and position=marketing
You can control the connection of the translated SAS WHERE clause and the SYSTEM 2000 where-clause conditions by including a connecting string in a SYSTEM 2000 where-clause included in a view descriptor. A connecting string indicates where you want the connection to occur. For example, suppose you have included the following SYSTEM 2000 where-clause in a view descriptor. (*SASAND* is one of the available connecting strings.)
You then issue a SAS procedure including a SAS WHERE clause that produces the following condition:
salary gt 1000
The resulting selection criteria are as follows:
salary gt 1000 and department=marketing
For more information and examples on the available connecting strings, see Connecting Strings.
When the interface view engine translates SAS WHERE clause conditions into SYSTEM 2000 conditions, there are SAS WHERE clause capabilities that are not available in SYSTEM 2000 software. Therefore, it is possible to issue a SAS WHERE clause that cannot be totally satisfied by SYSTEM 2000 software.
To allow for this possibility, the interface view engine first evaluates the SAS WHERE clause and determines whether the conditions can be handled. The interface view engine may be able to partially satisfy a SAS WHERE clause, as in the following example:
proc print data=vlib.emp1; where lastname < 'KAP' and payrate > 30 * overtime; run;
The interface view engine translates as much of the
SAS WHERE clause as possible, without producing incorrect results or a syntax
error from SYSTEM 2000 software. In the previous example, SYSTEM 2000 software
has no problem with the first condition, but the arithmetic in the second
condition is not supported. The interface view engine uses the condition
where lastname < 'KAP'to
filter out as many data records as possible to improve performance. The conditions
that are not supported are bypassed by the interface view engine, and post-processing
(handled automatically by the SAS System) will be required after SYSTEM 2000
software does its subsetting. The engine bypasses
When the interface view engine examines the SAS WHERE clause, it determines which conditions SYSTEM 2000 software can support. At this point, the engine has not processed the view descriptor where-clause. Later, when the engine processes the view descriptor where-clause, the possibility arises that the combined length of the SAS WHERE clause conditions acceptable to SYSTEM 2000 software and the view descriptor where-clause conditions could exceed 1000 bytes.
If the engine has determined that SYSTEM 2000 software completely supports the SAS WHERE clause, and then it determines that the conditions cannot be combined due to the 1000 byte limit, an unrecoverable error occurs. To the procedure or DATA step, it appears as though the first "read" observation failed. You may need to carefully examine the error messages on the log to determine what actually happened.
In the following table,, assume C114 is a component in the bottom record of a view descriptor. (Remember that if there is no SYSTEM 2000 where-clause included in the view descriptor and no SAS WHERE clause specified in the SAS program, the interface view engine issues a default where-clause in the form of WHERE Cn EXISTS OR Cn FAILS, where Cn is a component in the bottom record in the view descriptor.)
|View Where-Clause||SAS WHERE Clause||SYSTEM 2000 Translation||Post-Processing Required?|
|C1=A||C2=B OR C3>C4+10||(C1=A)||Yes|
|C1=A||C2=B & C3>C4+10||(C1=A) & (C2=B)||Yes|
|C1=A||C2=B OR C3>C4||(C1=A) & (C2=B OR C3*>C4*)||No|
|C1=A||C2=B & C3||(C1=A) & (C2=B)||Yes|
|---||---||C114 EXISTS OR C114 FAILS||No|
|---||C3*20 < C5||C114 EXISTS OR C114 FAILS||Yes|
|---||C3 = C5||C3* = C5*||No|
|SAS WHERE Clause Conditions Acceptable to SYSTEM 2000 Software|
The following information explains how the interface view engine translates acceptable SAS WHERE clause conditions into SYSTEM 2000 where-clause conditions.
|WHERE Clause Syntax||SYSTEM 2000 Translation|
|IS NOT NULL||EXISTS|
|WHERE Clause Syntax||SYSTEM 2000 Translation|
|C1 BETWEEN 1 AND 3||C1 = 1*3|
|C1 IN (4,9,14)||C1=4 OR C1=9 OR C1=14|
|C4 > C5||C4* > C5*|
|C4 = '02AUG87'D||C4 = 08/02/1987|
|Where Clause Syntax||SYSTEM 2000 Translation|
|C1 LIKE %ABC%||C1 CONTAINS ABC|
|C1 LIKE ABC%||C1 CONTAINS ABC IN 1|
|C1 LIKE _ABC%||C1 CONTAINS ABC IN 2|
|C1 LIKE __ABC%||C1 CONTAINS ABC IN 3|
|SAS WHERE Clause Conditions Not Acceptable to SYSTEM 2000 Software|
Here is a list of some (but not all) SAS WHERE clause conditions that are not acceptable to SYSTEM 2000 software; they are handled automatically by SAS post processing.
WHERE C1 = C4 * 3 WHERE C4 < -C5
WHERE C1 WHERE (C1 = C2) * 20
C1 =: ABC
WHERE C1 IS NULLif that is what you mean, not
WHERE C1 = .(The interface view engine can translate C1 IS NULL into C1 FAILS.)
|The NOT Operator|
The SAS WHERE clause NOT operator and the SYSTEM 2000 where-clause NOT operator do not function the same way. If you want NOT to have its SAS meaning, put it in the SAS WHERE clause. If you want NOT to have its SYSTEM 2000 software meaning, put it in the view descriptor where-clause.
If you specify NOT in a SAS WHERE clause, NOT is transformed by the SAS WHERE clause parser first; the interface view engine never sees the NOT operator. Consider the following examples:
|SAS WHERE Clause||What the Engine Sees|
|WH NOT LASTNAME = 'Jones';||WH LASTNAME NE 'Jones';|
|WH NOT LASTNAME > 'Baker';||WH LASTNAME <= 'Baker';|
|WH NOT (LASTNAME = JONES AND HIREDATE > '02aug82'd);||WH LASTNAME NE 'Jones' OR HIREDATE <= '02aug82'd;|
2000 software, however, the logical converse
wh not lastname = 'Jones'
lastname ne Jones.
Rather, the logical converse of
wh not lastname = 'Jones' is
wh lastname ne Jones or lastname fails. Before any relational operator
can find a match for a value, the value must exist. One reason for this is
that nulls are not contained in SYSTEM 2000 indexes, and processing an operator
such as NE could be expensive if it were not confined to indexed values.
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.