SAS WHERE Clause

Using a SAS WHERE Clause

In addition to (or instead of) including a WHERE clause in your view descriptor for selection criteria, you can also specify a SAS WHERE clause in a SAS program for selection criteria.

When you specify a SAS WHERE clause, the SAS/ACCESS interface view engine translates those conditions into view WHERE clause conditions. Then, if the view descriptor includes a WHERE clause, the interface view engine connects the conditions with the Boolean operator AND. By default, the SAS WHERE clause conditions are connected before the view WHERE clause 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

(position=marketing) and (sex=female)

When the interface view engine translates SAS WHERE clause conditions into view WHERE clause conditions, some SAS WHERE clause capabilities are not available in a view WHERE clause. That is, some SAS WHERE clauses cannot be totally satisfied by the interface view engine.

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 might 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. In the example above, the engine 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 logical records as possible to improve performance.

Any conditions that are not supported are bypassed by the interface view engine, and post-processing (handled automatically by SAS) is required after the engine does its subsetting. The engine bypasses the following conditions:

unacceptable conditions.
conditions connected with OR to unacceptable conditions.

In the following table, assume DF1, DF2, and DF3 are ADABAS data fields referenced by a view descriptor. Remember that SAS never sees view WHERE clauses.

Periodic Group Fields in WHERE Clauses

SAS WHERE Clause	View WHERE Clause	Translation	Processing Required?
DF2=B OR DF3>DF4+10	(DF1=A)	(DF1=A)	Yes
DF2=B & DF3>DF4+10	DF1=A	(DF2=B) & (DF1=A)	Yes
DF2=B & DF3>C	DF1=A	(DF2=B) & (DF3>C) & (DF1=A)	No
DF2=B OR DF3>C	DF1=A	(DF2=B) OR (DF3>C) & (DF1=A)	No

SAS WHERE Clause Conditions Acceptable to ADABAS

The following information explains how the interface view engine translates acceptable SAS WHERE clause conditions into view WHERE clause conditions.

The operators are translated as shown in the following table.

Acceptable SAS WHERE Clause Conditions in View WHERE Clause Conditions

SAS WHERE Clause Syntax	View WHERE Clause Translation
=	=
>	>
<	<
<>	!=
≥	≥
≤	≤
(	(
)	)
AND	AND
OR	OR

The interface view engine also translates BETWEEN and IN conditions and the date format (if a SAS format is supplied in the DB Content field).

Translating BETWEEN and IN Conditions and the Date Format

SAS WHERE Clause Syntax	View WHERE Clause Translation
DF1 BETWEEN 1 AND 3	(DF1 >= 1 AND DF1 <= 3)
DF1 IN (4,9,14)	DF1=4 OR DF1=9 or DF1=14
DF4 = '02AUG87'D	DF4 = 870802

SAS WHERE Clause Conditions Not Acceptable to ADABAS

Any SAS WHERE clause conditions that are not acceptable to the ADABAS interface view engine are handled automatically by SAS post-processing. The following are some (but not all) of those conditions:

item-to-item comparison
pattern matching
arithmetic expressions:
```
WHERE DF1 = DF4 * 3
WHERE DF4 < - DF5
```
expressions in which a variable or combination of variables assumes a value of 1 or 0 to signify true or false:
```
WHERE DF1
WHERE (DF1 = DF2) * 20
```
concatenation of character variables
truncated comparison:
```
DF1 =: ABC
```
DATETIME and TIME formats:
```
'12:00'T
'01JAN60:12:00'DT
```
SOUNDEX
HAVING, GROUP BY
NOT CONTAINS.

When a SAS WHERE Clause Must Reference Descriptor Data Fields

When you are using a SAS WHERE clause, a referenced ADABAS data field must be an ADABAS descriptor in the following situations:

The SAS WHERE clause contains more than one condition.
The SAS WHERE clause uses the SPANS or NE operator.
You are also planning to issue a SAS BY statement or a SAS ORDER BY clause.
The view descriptor also includes a view SORT clause.
The view descriptor also includes a view WHERE clause.