O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

proc sql;
alter table sql.newcountries
drop undate;
Creating an Index
An index is a file that is associated with a table. The index enables access to rows by
index value. Indexes can provide quick access to small subsets of data, and they can
enhance table joins. You can create indexes, but you cannot instruct PROC SQL to use
an index. PROC SQL determines whether it is efficient to use the index. Some columns
might not be appropriate for an index. In general, create indexes for columns that have
many unique values or are columns that you use regularly in joins.
Using PROC SQL to Create Indexes
You can create a simple index, which applies to one column only. The name of a simple
index must be the same as the name of the column that it indexes. Specify the column
name in parentheses after the table name. The following CREATE INDEX statement
creates an index for the Area column in NewCountries:
proc sql;
create index area
on sql.newcountries(area);
You can also create a composite index, which applies to two or more columns. The
following CREATE INDEX statement creates the index Places for the Name and
Continent columns in NewCountries:
proc sql;
create index places
on sql.newcountries(name, continent);
To ensure that each value of the indexed column (or each combination of values of the
columns in a composite index) is unique, use the UNIQUE keyword:
proc sql;
create unique index places
on sql.newcountries(name, continent);
Using 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.
Tips for Creating Indexes
The name of the composite index cannot be the same as the name of one of the
columns in the table.
If you use two columns to access data regularly, such as a first name column and a
last name column from an employee database, then you should create a composite
index for the columns.
Keep the number of indexes to a minimum to reduce disk space and update costs.
Use indexes for queries that retrieve a relatively small number of rows (less than
15%).
In general, indexing a small table does not result in a performance gain.
Creating an Index 127

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required