Consider three sample queries to see how you can apply the guidelines that are listed in the previous section. These queries illustrate the effect of one factor—the size of the subset relative to the size of the data set—on the choice of an access method. For each query, you learn the following:

Suppose you are working with the following two data sets. Each contains information about a company's orders:

You want to create queries to generate three subset detail reports, one for each of the following types of subsets:

In all three queries, the WHERE expression specifies the variable Order_Date. You know that this variable is used frequently in queries, and that it is a discriminating variable. According to the guidelines in the previous section, these are both criteria for creating an index on the variable. However, there is currently no index defined on this variable in either data set.

The first report shows all orders in Company.Orders_large that were made on January 10, 1998. Your query is shown below, along with the subset size that you have estimated:

data _null_;
    set company.orders_large;
    where order_date='10JAN1998'd;
run;

Because the subset is less than 3% of the entire data set, using an index on Order_Date should be more efficient than using sequential access. SAS uses an index for WHERE processing, if an index is available. To improve performance, you should create an index on Order_Date before running this program.

The second report shows all orders in Company.Orders_large that were made before January 1, 2000. Your query and the estimated subset size are shown below:

data _null_;
   set company.orders_large;
   where order_date<'01JAN2000'd;
run;

Because the subset is more than 33% of the entire data set, using the index is probably less efficient than using sequential access. SAS probably will not use the index for WHERE processing.

The third report shows all orders in the smaller data set Company.Orders_small that were made on June 30, 1998. Your query and the estimated subset size are shown below:

data _null_;
   set company.orders_small;
   where order_date'30JUN1998'd;
run;

Because the subset is less than 3% of the entire data set, SAS will use the index for WHERE processing. However, the data file's page count is less than three pages, so it is more efficient to use sequential access. In this situation, it is best not to create an index.

In most situations, it is best to let SAS determine whether to use an index for WHERE processing. However, sometimes you might want to control whether SAS uses an existing index. For example, if you know that your query selects a large subset and that indexed access therefore is not efficient, you can tell SAS to ignore any index and to satisfy the conditions of the WHERE expression with a sequential search of the data set. Alternatively, if your query selects a small subset and there are multiple available indexes, you can make sure that SAS uses a particular index to process your WHERE statement. Finally, you might want to force SAS to use (or not use) an index when you are benchmarking.

The data set options IDXWHERE= and IDXNAME= control index usage.

To determine whether SAS used an index to process a WHERE expression, specify I as the value of the MSGLEVEL= system option. MSGLEVEL=I causes SAS to display information about index usage in the SAS log.

Suppose you write the following query, which lists all employees who work in the Sales department.

proc print data=company.organization;
   where department='Sales';
run;

Now suppose an index is defined on the variable Department in the data set Company.Organization. You know that Department has the value Sales in 65% of the observations, so it is not efficient for SAS to use an index for WHERE processing. To ensure that SAS does not use an index, specify IDXWHERE=NO after the data set name. At the beginning of the program, you can also add an OPTIONS statement that specifies MSGLEVEL=I to display a message about index usage in the SAS log. The revised program is shown below:

options msglevel=i;
proc print data=company.organization (idxwhere=no);
   where department='Sales';
run;

When you run this program, the SAS log indicates that the index was not used for processing.

INFO: Data set option (IDXWHERE=NO) forced a sequential pass of the data 
rather than use of an index for where-clause processing.