Previous Page | Next Page

Advanced Topics


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


and the SAS WHERE clause condition translates into


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;

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:

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.

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:

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:

Previous Page | Next Page | Top of Page