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 and PROC steps
Category:	User Control of SAS Index Usage
Restrictions:	Use with input data sets only. Mutually exclusive with IDXNAME= data set option
Supports:	SAS data set

Syntax

Details

Comparisons

Examples

Example 1: Specifying Index Usage

Example 2: Specifying No Index Usage

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.

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 occasionally cannot 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.

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