The SQL Procedure

CREATE INDEX Statement

Creates indexes on columns in tables.

Restriction: You cannot use CREATE INDEX on a table accessed via an engine that does not support UPDATE processing.

CREATE <UNIQUE> INDEX index-name

ON table-name (column <, column>...);

Arguments

column: specifies a column in table-name.
index-name: names the index that you are creating. If you are creating an index on one column only, index-name must be the same as column. If you are creating an index on more than one column, index-name cannot be the same as any column in the table.
table-name: specifies a PROC SQL table.

Indexes in PROC SQL
An index stores both the values of a table's columns and a system of directions that enable access to rows in that table by index value. Defining an index on a column or set of columns enables SAS, under certain circumstances, to locate rows in a table more quickly and efficiently. Indexes enable PROC SQL to execute the following classes of queries more efficiently:

comparisons against a column that is indexed
an IN subquery where the column in the inner subquery is indexed
correlated subqueries, where the column being compared with the correlated reference is indexed
join-queries, where the join-expression is an equals comparison and all the columns in the join-expression are indexed in one of the tables being joined.

SAS maintains indexes for all changes to the table, whether the changes originate from PROC SQL or from some other source. Therefore, if you alter a column's definition or update its values, the same index continues to be defined for it. However, if an indexed column in a table is dropped, the index on it is also dropped.

You can create simple or composite indexes. A simple index is created on one column in a table. A simple index must have the same name as that column. A composite index is one index name that is defined for two or more columns. The columns can be specified in any order, and they can have different data types. A composite index name cannot match the name of any column in the table. If you drop a composite index, the index is dropped for all the columns named in that composite index.

UNIQUE Keyword
The UNIQUE keyword causes the SAS System to reject any change to a table that would cause more than one row to have the same index value. Unique indexes guarantee that data in one column, or in a composite group of columns, remain unique for every row in a table. For this reason, a unique index cannot be defined for a column that includes NULL or missing values.

Managing Indexes
You can use the CONTENTS statement in the DATASETS procedure to display a table's index names and the columns for which they are defined. You can also use the DICTIONARY tables INDEXES, TABLES, and COLUMNS to list information about indexes. See DICTIONARY tables .

See the section on SAS files in SAS Language Reference: Dictionary for a further description of when to use indexes and how they affect SAS statements that handle BY-group processing.

Chapter Contents
Previous
Next
Top of Page