SAS/ACCESS Interface to SYSTEM 2000 Data Management Software: Reference

Using a SAS WHERE Clause for Selection Criteria

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.) [cautionend]

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

   sex=female

and the SAS WHERE clause condition translates into

   position=marketing

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.)

   *sasand* department=marketing

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

unacceptable conditions.
conditions connected with OR to unacceptable conditions.
conditions that exceed a SYSTEM 2000 where-clause 1000-byte limit. If the SAS WHERE clause exceeds 1000 bytes, the rightmost portion of it is bypassed by SYSTEM 2000 software.
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

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.

The following operators are translated as indicated:

WHERE Clause Syntax SYSTEM 2000 Translation

= =

> >

< <

<> !=

>= >=

<= <=

IS NULL FAILS

IS NOT NULL EXISTS

( (

) )

AND AND

OR OR

WHERE Clause Syntax	SYSTEM 2000 Translation
=	=
>	>
<	<
<>	!=
>=	>=
<=	<=
IS NULL	FAILS
IS NOT NULL	EXISTS
(	(
)	)
AND	AND
OR	OR

The interface view engine also translates BETWEEN and IN conditions, item-to-item comparisons, and the date format:

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 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

SYSTEM 2000 software can handle a limited subset of SAS WHERE clause pattern matching, with the following prerequisites:

The pattern must be less than 100 characters long.
The pattern must have % as the rightmost character.
Underscores are permitted only in leftmost position(s).
The pattern cannot have % anywhere but in the leftmost or rightmost position.
The pattern must have some characters that are not % or _.

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

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.

arithmetic expressions, for example,
```
WHERE C1 = C4 * 3
WHERE C4 < -C5
```
expressions in which a variable or combination of variables assumes a value of 1 or 0 to signify true or false, for example,
```
WHERE C1 
WHERE (C1 = C2) * 20
```
concatenation of character variables
truncated comparison, for example,
```
    C1 =: ABC
```
DATETIME and TIME formats, for example,
```
'12:00'T 
'01JAN60:12:00'DT
```
SOUNDEX
HAVING, GROUP BY
NOT CONTAINS
references to '.' for numeric variables or ' ' for character variables. Use WHERE C1 IS NULL if 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;

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;

In SYSTEM 2000 software, however, the logical converse of wh not lastname = 'Jones' is not 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.

Chapter Contents
Previous
Next
Top of Page