SPD Engine Data Set Options

WHERENOINDEX= Data Set Option

Specifies a list of indexes to exclude when making WHERE expression evaluations.

Valid in:	DATA step and PROC step
Default:	blank
Restriction:	cannot be used with IDXWHERE=NO data set option

Syntax
Example

Syntax

WHERENOINDEX=(name1 name2...)

(name1 name2...): a list of index names to exclude from the WHERE planner.

Example

The data set PRECS is defined with indexes:

proc datasets lib=spde cen
   modify precs;
   index create stser=(state serialno) occind=(occup industry) hour89;
quit;

When evaluating the next query, the SPD Engine does not use the indexes for either the STATE or HOUR89 variables.

In this case, the AND combination of the conditions for the OCCUP and INDUSTRY variables produce a very small yield. Few observations satisfy the conditions. To avoid the extra index I/O (computer time) that the query requires for a full-indexed evaluation, use the following SAS code:

proc sql;
   create data set hr80spde
   as select state, age, sex, hour89, industry, occup from spde cen.precs
     (wherenoindex=(stser hour89))
   where occup='022'
   and state in('37','03','06','36')
   and industry='012'
   and hour89 > 40;
 quit;

Note: Specify the index names in the WHERENOINDEX list, not the variable names. In the previous example, both the composite index for the STATE variable, STSER, and the simple index, HOUR89, are excluded. [cautionend]

Top of Page