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
Syntax Description
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. The decision is based on general rules that might not always produce the best results. By specifying the IDXWHERE= data set option, you can 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, whereas INDEXWHERE= enables you to override the SAS decision about whether to use an 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 perform the following tasks:
  • tell SAS to ignore any index
  • 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

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