When processing a query that contains a subsetting WHERE clause or that joins multiple tables, PROC SQL must locate specific rows in the referenced table(s). Creating an index for a table enables PROC SQL, in certain circumstances, to locate specific rows more quickly and efficiently. An index is an auxiliary file that stores the physical location of values for one or more specified columns (key columns) in a table. In an index, each unique value of the key column(s) is paired with a location identifier for the row that contains that value. In the same way that you use a book's subject index to find a page that discusses a particular subject, PROC SQL uses the system of directions in an index to access specific rows in the table directly, by index value. You can create more than one index for a single table. All indexes for a SAS table are stored in one index file.

The following PROC SQL step uses the CREATE INDEX statement to create an index for a table, and uses the DESCRIBE TABLE statement to display information about the index, along with other information about the table, in the SAS log:

proc sql;
   create unique index empid
      on work.payrollmaster(empid);
   describe table work.payrollmaster;

Table 6.1. SAS Log

In this chapter, you will learn to use PROC SQL to create and manage various types of indexes.

In this chapter, you learn to

Before reading this chapter, you should complete the following chapters:

When you submit a query on a table that does not have an index, PROC SQL accesses rows sequentially, in the order in which they are stored in the table. For example, suppose you are working with a table that contains information about employees. You have written a PROC SQL query to select the rows in which the value of Name (the first column) is Smith. To access the rows that you want, PROC SQL begins with the first row and reads through all rows in the table, selecting the rows that satisfy the condition that is expressed in the WHERE clause.

When you execute a program that retrieves a small subset of rows from a large table, it can be time-consuming for PROC SQL to read the rows sequentially. In some situations, using an index on a table allows PROC SQL to access a subset of rows more efficiently.

An index stores unique values for a specified column or columns in ascending value order, and includes information about the location of those values in the table. That is, an index includes value/identifier pairs that enable you to access a row directly,by value. For example, suppose you have created an index on your table that is based on the column Name. Using the index, PROC SQL will access the row(s) that you want directly, without having to read all the other rows.

You can create two types of indexes:

A simple index is based on one column that you specify. The indexed column can be either character or numeric. When you create a simple index by using PROC SQL, you must specify the name of the indexed column as the name of the index.

A composite index is based on two or more columns that you specify. The indexed columns can be character, numeric, or a combination of both. In the index, the values of the key columns are concatenated to form a single value.

For example, if you build a composite index on the key columns LastName and FirstName, a value for the index is composed of the value for LastName followed by the value for FirstName. Often, a WHERE clause might use only the first column (the primary key) of a composite index, which means that the program will read only the first part of each concatenated value.

When you create a composite index, you must specify a unique name for the index that is not the name of any existing column or index in the table. In the example described above, the composite index cannot be named Lastname or Firstname.

If you want to require that values for the key column(s) are unique for each row, you can create either a simple or a composite index as a unique index. Once a unique index is defined on one or more columns in a table, SAS will reject any change to the table that would cause more than one row to have the same value(s) for the specified column or composite group of columns.

Suppose you are working with the table Sasuser.Payrollmaster. The first eight rows of this table are shown below.

If you know that the column JobCode is often specified in a WHERE clause expression, you might want to create a simple index on the column JobCode. You must specify the name of the key column, JobCode, as the index name.

Now suppose you are planning to write many queries that specify both EmpID and DateOfHire in a WHERE clause expression. In this case, you might want to create a composite index on these two columns. Because employee identification numbers should be unique, it is appropriate to create this index as a unique index. Therefore, you should specify a name for your index that is not the same as the name of any existing column or index in the table. For example, you could name this index Whenhired.

To use indexes effectively with PROC SQL, it is important to know the classes of queries that can be processed more efficiently by using an index. The classes of queries that can be optimized are specified below.

proc sql;
    select empid, jobcode, salary
      from sasuser.payrollmaster
      where jobcode='FA3' 
      order by empid;

proc sql;
   select empid, lastname, firstname,
          city, state
      from sasuser.staffmaster
      where empid in 
         (select empid 
            from sasuser.payrollmaster 
            where salary>40000); 

proc sql;
   select lastname, firstname
      from sasuser.staffmaster
      where 'NA'=
         (select jobcategory 
            from sasuser.supervisors 
            where staffmaster.empid = 
                  supervisors.empid); 

proc sql;
   select * 
      from sasuser.payrollmaster as p, 
           sasuser.staffmaster as s 
      where p.empid = 
            s.empid 
      order by jobcode; 

For PROC SQL, there are three main benefits to using an index to access data directly (instead of reading the data sequentially):

Suppose you are writing a query that references the table Work.Payrollmaster. (Work.Payrollmaster is a duplicate of the table Sasuser.Payrollmaster.)

Work.Payrollmaster stores payroll information for employees, and it has a simple index defined on the column JobCode. Your query's WHERE clause expression references the key column:

proc sql;
   select empid, jobcode, salary
      from work.payrollmaster
      where jobcode='FA3' 
      order by empid;

If the value of JobCode for most of the rows in the table is FA3, then the use of an index will not significantly improve the efficiency of the following query. In fact, performance might be degraded.

However if only 10% of the rows have a value of FA3, then PROC SQL can process the query more efficiently by using the index.

When you are deciding whether to create an index, you should consider the associated increase in resource usage, which includes the following:

To use indexes effectively, follow these guidelines for creating indexes:

You cannot create multiple simple indexes that are based on the same column or multiple composite indexes that are based on the same set of columns. Although it is possible to create both a simple and a composite index on the same column, it is usually not advantageous to do this. If a simple index is defined on a column and that column is also the primary key in a composite index, PROC SQL will use the composite index in processing a query that references that column.

You can create multiple indexes on the same table, but you must use a separate CREATE INDEX statement for each index that you want to create.

The following PROC SQL step uses the CREATE INDEX statement to create a simple, unique index that is based on the column EmpID in the temporary table Work.Payrollmaster.(Work.Payrollmaster is a duplicate of the table Sasuser.Payrollmaster.)

proc sql;
   create unique index EmpID 
      on work.payrollmaster(empid); 

The specified index name (EmpID) must be the same as the name of the key column. When this step is submitted, the SAS log displays the following message.

NOTE: Simple index EmpID has been defined.

The following PROC SQL step uses the CREATE INDEX statement to create the composite, unique index daily on the columns FlightNumber and Date:

proc sql;
      create unique index daily 
      on work.marchflights(flightnumber,date); 

When this step is submitted, the SAS log displays the following message.

NOTE: Composite index daily has been defined.

If the set of key columns FlightNumber and Date had duplicate values, the index would not be created. Instead, the SAS log would display a message like the following.

The DESCRIBE TABLE statement is only one of several methods that can be used to list information about indexes that are defined on a table. One alternative is to query the special table Dictionary.Indexes, which contains information about indexes that are defined for all tables that are known to the current SAS session. (Dictionary.Indexes is one of many read-only dictionary tables that are created at PROC SQL initialization. These tables contain information about SAS libraries, SAS macros, and external files that are in use or available in the current SAS session.)

You can also use other SAS procedures, such as PROC CONTENTS and PROC DATASETS, to generate a report that contains information about indexes.

Earlier in this chapter, the following code was used to create a unique composite index named daily on the columns FlightNumber and Date in the temporary table Marchflights.

proc sql;
    create unique index daily
      on work.marchflights(flightnumber,date);

The following DESCRIBE TABLE statement writes a CREATE INDEX statement to the SAS log (after the CREATE TABLE statement) for the table Marchflights:

proc sql;
      describe table marchflights;

Table 6.5. SAS Log

If the table Marchflights had no index defined, no CREATE INDEX statement would appear in the SAS log.

By default, each time you submit a query (or other SAS program) that contains a WHERE expression, SAS decides whether to use an index or to read all the observations in the data file sequentially. To make this decision, SAS does the following:

Next, consider how you can find out whether SAS is using an index.

After you create an index, it is important to monitor whether the index is being used. If an index is not being used, the costs of maintaining the index might be greater than the benefits, and you should consider dropping (deleting) the index.

By default, when a PROC SQL query or any other program is submitted in SAS, only notes, warnings, and error messages are written to the SAS log. To display additional messages, such as information about indexes that have been defined and that have been used in processing the program, specify the SAS system option MSGLEVEL=I.You specify the MSGLEVEL= option in the OPTIONS statement, before the PROC SQL statement.

Usually, the option MSGLEVEL= is set to I for debugging and testing, and set to N for production jobs.

Suppose you are writing a PROC SQL query that references the temporary table Marchflights. Earlier in this chapter, a unique composite index named daily was created on the columns FlightNumber and Date in Marchflights. The WHERE expression in your query specifies the key column FlightNumber. To determine whether PROC SQL uses the index daily when your query is processed, you specify MSGLEVEL=I before the query:

options msglevel=i; 
proc sql;
   select *
      from marchflights
      where flightnumber='182';

The message in the SAS log shows that the index was used in processing.

Suppose you submit a different query that also references the key column FlightNumber:

proc sql;
    select *
      from marchflights
      where flightnumber in ('182','202'),

In this example, the SAS log shows that the query does not use the index.

In general, it is recommended that you allow SAS to decide whether to use an index, or which index to use, in processing a PROC SQL query (or other SAS program). However, in some situations, such as testing, you might find it useful to control the use of indexes by SAS.

To control index usage, use the IDXWHERE= and IDXNAME= SAS data set options to override the default settings. You can use either of these options, but you cannot use both options at the same time. As with other SAS data set options, you specify the IDXWHERE= or IDXNAME= option in parentheses after the table name in the FROM clause of a PROC SQL query.

The IDXWHERE= option enables you to override the decision that SAS makes about whether to use an index.

In an earlier example, you used the option MSGLEVEL=I to verify that PROC SQL does use an index to process the following query:

options msglevel=i;
proc sql;

select *
   from marchflights
   where flightnumber='182';

To force SAS to ignore the index and to process the rows of the table sequentially, specify IDXWHERE=NO in the query:

proc sql;
   select *
      from marchflights (idxwhere=no) 
      where flightnumber='182';

A message in the SAS log indicates that SAS was forced to process the data sequentially.

The IDXNAME= option directs SAS to use an index that you specify, even if SAS would have selected not to use an index or to use a different index.

SAS uses the specified index if the following conditions are true:

In an earlier example, a composite index named daily was defined on the columns FlightNumber and Date in the temporary table Marchflights. Suppose you create a second index, a simple index, on the column Date (the secondary key in the composite index) by using the following PROC SQL step:

proc sql;
    create index Date
      on work.marchflights(Date);

Next, you submit the following query:

proc sql;
   select *

     from marchflights
     where date='01MAR2000'd; 

The WHERE clause in this query references the key column Date. By default, SAS decides whether to use an index and, if an index is used, which index to use. The SAS log indicates that, with both a simple and a composite index defined on Date, PROC SQL used the simple index Date to process the query.

You decide that you want to force PROC SQL to use the index daily instead of Date, so you add IDXNAME= to your query:

proc sql;
   select *
      from marchflights (idxname=daily) 
      where flightnumber='182';

After this query is submitted, a message in the SAS log indicates that PROC SQL used the index daily:

The following PROC SQL step uses the DROP INDEX statement to drop the composite, unique index daily from the temporary table Marchflights. (This index was created in an example earlier in this chapter.)

proc sql;
    drop index daily
      from work.marchflights;

When this step is submitted, the SAS log displays a message indicating that the index has been dropped.

NOTE: Index daily has been dropped.

  OPTIONS MSGLEVEL=N | I;

    PROC SQL;

      CREATE <UNIQUE>INDEX index-name

        ON table-name (column-name-1<, …column-name-n>);

      DESCRIBE TABLE table-name-1<, … table-name-n>;

      SELECT column-1<, … column-n>

        FROM table-1 (IDXWHERE=Y|N)

        <WHERE expression>;

      SELECT column-1<, . . . column-n>

        FROM table-1 (IDXNAME=index-name)

        <WHERE expression>;

       DROP INDEX table-name-1<, … table-name-n>;

         FROM table-name;

     QUIT;