The SQL Procedure

CREATE INDEX Statement

Creates indexes on columns in tables.

Restriction:

You cannot use CREATE INDEX on a table that is accessed with 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, then index-name must be the same as column. If you are creating an index on more than one column, then 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, then the same index continues to be defined for it. However, if an indexed column in a table is dropped, then 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, then the index is dropped for all the columns named in that composite index.

UNIQUE Keyword

The UNIQUE keyword causes SAS 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, remains unique for every row in a table. A unique index can be defined for a column that includes NULL or missing values if each row has a unique index value.

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. For more information, see Accessing SAS System Information Using DICTIONARY Tables in the SAS 9.2 SQL Procedure User's Guide.

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.

Top of Page