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 |
Engine: |
SPD Engine only |
Syntax
WHERENOINDEX=(name1 name2...)
Required Argument
- (name1 name2...)
-
a list of index names
to exclude from the WHERE planner.
Example: Excluding indexes
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.