IDXWHERE= Data Set Option

Specifies whether SAS uses an index search or a sequential search to match the conditions of a WHERE expression.
Valid in: DATA step and PROC steps
Category: User Control of SAS Index Usage
Restrictions: Use with input data sets only.

Mutually exclusive with IDXNAME= data set option
Syntax
Details
Comparisons
Examples
Example 1: Specifying Index Usage
Example 2: Specifying No Index Usage
See Also

Syntax

IDXWHERE=YES | NO

Syntax Description

YES
tells SAS to choose the best index to optimize a WHERE expression, and to disregard the possibility that a sequential search of the data set might be more resource-efficient.
NO
tells SAS to ignore all indexes and satisfy the conditions of a WHERE expression with a sequential search of the data set.
Note: You cannot use IDXWHERE= to override the use of an index to process a BY statement.

Details

By default, to satisfy the conditions of a WHERE expression for an indexed SAS data set, SAS decides whether to use an index or to read the data set sequentially. The software estimates the relative efficiency and chooses the method that is more efficient.
You might need to override the software's decision by specifying the IDXWHERE= data set option because the decision is based on general rules that might occasionally not produce the best results. That is, by specifying the IDXWHERE= data set option, you are able to determine the processing method.
Note: The specification is not a permanent attribute of the data set and is valid only for the current use of the data set.
Note: If you issue the system option MSGLEVEL=I, you can request that IDXWHERE= usage be noted in the SAS log if the setting affects index processing.

Comparisons

IDXNAME= enables you to direct SAS to use a specific index.

Examples

Example 1: Specifying Index Usage

This example uses the IDXWHERE= data set option to tell SAS to decide which index is the best to optimize the WHERE expression. SAS then disregards the possibility that a sequential search of the data set might be more resource-efficient: 
data mydata.empnew;
   set mydata.employee (idxwhere=yes);
   where empnum < 2000;

Example 2: Specifying No Index Usage

This example uses the IDXWHERE= data set option to tell SAS to ignore any index and to satisfy the conditions of the WHERE expression with a sequential search of the data set: 
data mydata.empnew;
   set mydata.employee (idxwhere=no);
   where empnum < 2000;

See Also

Data Set Option:
IDXNAME= Data Set Option
Other:
Understanding SAS Indexes in SAS Language Reference: Concepts
WHERE-Expression Processing
Copyright © SAS Institute Inc. All rights reserved.