Chapter Contents


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


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

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

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

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.

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.

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 > 'Baker'; WH LASTNAME <= 'Baker';

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



Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.