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 accesses 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 consists 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 reads 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 index or a composite index as a unique index. Once a unique index is defined on one or more columns in a table, SAS rejects 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.