SAS Component Language Dictionary

ICREATE

Creates an index for a SAS table

Category:

SAS Table

Syntax
Details
Example
See Also

Syntax

sysrc=ICREATE(table-id,key-name<,var-list<,options>>);

sysrc

contains the return code for the operation:

0	successful
0	not successful

Type: Numeric

table-id

is the identifier that was assigned when the table was opened. If table-id is invalid or if the table is not opened in UTILITY mode, the index is not created and the function returns a nonzero value.

Type: Numeric

key-name

is the name of the index key to be created.

Type: Character

var-list

is one or more columns from the SAS table to be indexed. Separate multiple names with blanks.

Type: Character

options

are index attributes, with multiple values separated by blanks within a single set of parentheses:

'NONUNIQUE' |'UNIQUE': specifies whether the values of the key columns must be unique. The default is 'NONUNIQUE'.
'MISSING' |'NOMISS': specifies whether the index can point to missing values. The default is 'MISSING'.

Type: Character

Details

An index is an auxiliary data structure used to speed up searches for records that are specified by the value of a column (for example, "all the records for which AGE is greater than 65"). To create an index for a SAS table, you must open the table in UTILITY mode (see OPEN for details).

An index on a single column is called a simple index. If var-list contains only one column name, then a simple index is created.

Note: For a simple index, key-name and var-list must both contain the same value. If var-list is omitted, then key-name specifies the index column. [cautionend]

An index on more than one column is called a composite index. If var-list contains more than one column name, a composite index is created. In this case, key-name can be any valid SAS name that is not already used as a column name in the table. A composite index is based on the values of these columns, concatenated to form a single value.

UNIQUE specifies that the index contains only unique values of the key columns. The creation of such an index prohibits duplicate values for its columns from being stored in the SAS table. For columns that must be uniquely specified in a row, such as passport numbers, this option is useful for preventing duplicate values from incorrectly getting into a table. The function returns an error condition if non-unique values are present and UNIQUE is specified. By default, duplicate values are permitted in an index and thus in its table.

NOMISS prevents missing values from being pointed to by an index. Unlike UNIQUE, NOMISS does not prevent missing values from being stored in the SAS table. This feature is useful if the key columns contain many missing values that would make the index large and thus slower to access than if they were excluded. By default, missing values are stored in the index.

Indexes can also be created using

the DATASETS and SQL procedures in Base SAS software
SAS/IML software
the CONTENTS function in SCL
the ACCESS procedure in SAS/ACCESS software.

Example

Create a simple index for the SAS table WORK.INVOICE. The key column for the index is the table column ITEMTYPE.

tableid=open('work.invoice','v');
   /* open in UTILITY mode */
rc=icreate(tableid,'itemtype',' ','unique nomiss');

In this example, because the value of the var-list argument is blank, the key column for the index is the column named in the key-name argument.