When using an index to select a subset, SAS loads only the pages that contain at least one qualified observation into input buffers. When accessing observations sequentially, SAS must load all pages into input buffers. Loading more pages requires more I/O operations.

SAS does not use an index for a WHERE condition that contains a function other than TRIM or SUBSTR.

The size of the subset relative to the size of the data set is an important factor in determining which access method is most efficient. If a subset is large (more than 33% of the data set), it is likely to be more efficient to use sequential access than direct access. Direct access is usually more efficient when you select a small subset (less than 33% of the data set), especially if the data set is large (has a high page count). However, if the data set is very small (less than three pages), using an index is not efficient. The number of key variables specified in a WHERE expression does not determine which access method is most efficient. If the two key variables that are specified are the first two variables in the same index, the WHERE expression is a candidate for compound optimization. Sorting the data also does not determine which access method is most efficient. However, sorting the data before subsetting improves the efficiency of WHERE processing regardless of the access method.