Using SAS Tables

Manipulating SAS Table Indexes in SCL Programs

When you develop an application that creates a SAS table, you may want to give users the option of creating an index for the table. An index, which provides fast access to rows, is an auxiliary data structure that specifies the location of rows, based on the values of one or more columns, known as key columns. Both compressed and uncompressed SAS tables can be indexed by one or more columns to aid in the subsetting, grouping, or joining of rows. SAS table indexes are particularly useful for optimizing WHERE clause processing.

SCL provides a set of functions for creating and manipulating SAS table indexes. However, SCL functions are just one way of building and querying SAS table indexes. Other ways include:

the DATASETS procedure in Base SAS software
the INDEX= option (when you are creating a SAS table)
the SQL procedure in Base SAS software.

There are two types of indexes: simple indexes and composite indexes. A simple index is an index on a single column, and a composite index is an index on more than one column. A SAS table can have multiple simple indexes, multiple composite indexes, or a combination of simple and composite indexes.

SCL provides the following functions for manipulating indexes:

ICREATE

creates an index for SAS tables that are open in UTILITY mode.

IVARLIST

returns a list of one or more columns that have been indexed for the specified key in the table.

ISINDEX

returns the type of index for a column in a SAS table, as follows:

BOTH	The column is a member of both simple and composite indexes.
COMP	The column is a member of a composite index.
REG	The column is a member of a regular (simple) index.
(blank)	No index has been created for the specified column.

IOPTION

returns a character string that consists of the options for the specified key and index columns. The options are separated by blanks.

IDELETE

deletes an index for a SAS table that is open in UTILITY mode. You can delete an index when a program finishes with it, or if you find that the index is not operating efficiently. Keep in mind that indexes are not always advantageous. Sometimes the costs outweigh the savings. For a detailed discussion of when to use indexes, see the information about SAS files in SAS Language Reference: Concepts.

Top of Page