Written and maintained by CASRAI Editorial Board
Published
On this page
Indexing in SAS means creating an index, an optional companion file that gives SAS direct access to the observations of a data set by the values of one or more variables, so a WHERE, BY or KEY= request does not have to read every observation in storage order. You create one with the INDEX= data set option, the INDEX CREATE statement in PROC DATASETS, or CREATE INDEX in PROC SQL, and SAS decides at run time whether to use it.
What SAS indexes are used for in research
In research data work, an index pays off when you repeatedly pull a small subset out of a large data set: all records for one participant ID, one site, or one visit window. According to the SAS Concepts documentation, an index stores the values of the indexed variable in ascending order together with record identifiers, and it is updated automatically when observations are added, deleted or changed. SAS can then use it for WHERE processing, for BY-group processing in index order without a PROC SORT, for direct retrieval with the KEY= option on SET and MODIFY, and in PROC SQL joins. It is never used for a subsetting IF statement.
Simple and composite indexes
A simple index is built on one variable and takes that variable’s name. A composite index is built on two or more variables and needs its own unique name. Two index options matter for study data: UNIQUE, which rejects duplicate values (useful for a participant identifier that must never repeat), and NOMISS, which leaves observations with missing values out of the index.
Not to be confused with array subscripts or the INDEX function
This page is about data set indexes. Array subscripts are covered in the guide to SAS arrays, and the INDEX function that searches character strings is a separate topic. An index here is a file-level access path, not a position inside a variable or an array.
How to install or access SAS
Data set indexes are a feature of SAS data files described in SAS Concepts, so the statements below run in any SAS session that can write to a library. SAS lists SAS Viya for Learners as free for students and educators on its academic programs page, and describes it as including a ready-made JupyterLab setup for working with R and Python alongside SAS. Researchers outside a student or educator role should check which SAS products and licences their institution provides and where the vendor’s own pages direct them.
When to create an index: a checklist from the SAS documentation
An index is not free: it takes disk space, and every update to the data set also updates the index. The SAS documentation gives these guidelines. They come from different pages and use different thresholds, so treat them as rules of thumb, not as a fixed cut-off.
| Question | Guideline | Documentation page |
|---|---|---|
| How small a subset justifies an index? | Consider one when you retrieve less than 25% of all observations; SAS generally uses an index when it estimates a WHERE expression selects about one-third or less; below 3% SAS uses it automatically. | Understanding SAS Indexes (SAS Concepts) |
| Is the data set too small? | Do not index a data file with fewer than three pages; indexing a small table in PROC SQL generally gives no gain. | Understanding SAS Indexes; PROC SQL CREATE INDEX |
| Is the key too uniform? | A column with only a handful of distinct values (about 6 or 7 or fewer) generally gives no gain in PROC SQL. | PROC SQL CREATE INDEX |
| PROC SQL subset threshold | Indexes help most for queries that retrieve under about 15% of rows. | PROC SQL CREATE INDEX |
| How many indexes? | Keep them few, because of disk space, CPU and update cost. | Understanding SAS Indexes; PROC SQL CREATE INDEX |
| Composite index order | Put the most discriminating variable first. | Understanding SAS Indexes |
The 25% figure on the SAS Concepts page and the 15% figure on the PROC SQL page are both quoted here as the documentation states them. Neither replaces measuring your own data: build the index, run your real WHERE step with the log option below, and see whether SAS reports using it.
How to create an index in SAS step by step
The three methods below produce the same kind of index. The examples use a hypothetical data set, work.visits, with variables subject_id, site and visit_no. This is illustrative code, not output from a real study.
Method 1: the INDEX= data set option when you create the data set
- Write the DATA step that builds the new data set.
- Add
index=(...)as a data set option on the output data set. Per the INDEX= documentation it applies to output data sets only, in DATA and PROC steps. - Put UNIQUE or NOMISS after a slash inside the specification, and name a composite index by writing
name=(var1 var2). - Run the step.
data work.visits(index=(subject_id /unique site_visit=(site visit_no)));
set raw.visits;
run;
The documentation’s own examples follow the same pattern, for instance data new(index=(ssn /unique)) and data new(index=(cityst=(city state))). If you request UNIQUE on a new data set and the data contain duplicates, the index is not built.
Method 2: PROC DATASETS INDEX CREATE on an existing data set
- Start
proc datasets library=work;. - Name the data set in a
modifystatement. The documentation says INDEX CREATE must be in a MODIFY run group. - Write
index createfollowed by a variable name for a simple index, orname=(var1 var2)for a composite one. A composite index needs at least two variables, and its name must be a valid SAS name that does not match any variable name or other composite index name. - Optionally add NOMISS, UNIQUE or UPDATECENTILES= as described on the INDEX CREATE page, then finish with
run;andquit;.
proc datasets library=work;
modify visits;
index create subject_id;
index create site_visit=(site visit_no);
run;
quit;
With UNIQUE, the documentation states that if multiple observations share the same values the index is not created. With NOMISS, observations that are missing on all index variables are left out, the index is used only for WHERE processing, and BY-group processing ignores it. UPDATECENTILES= controls when SAS refreshes the centile statistics it keeps for the index; the default is 5 percent of indexed values changing, and ALWAYS or NEVER are also accepted.
Method 3: PROC SQL CREATE INDEX
- Open
proc sql;. - Write
create index(orcreate unique index), the index name, thenonthe table name with the columns in parentheses, separated by commas. - For a simple index, the name must be the same as the column it indexes. For a composite index, the name must differ from every column name in the table.
- To remove an index, use
drop indexwith the index name andfromthe table. Dropping a composite index removes it for all the columns it names.
proc sql;
create unique index subject_id on work.visits(subject_id);
create index site_visit on work.visits(site, visit_no);
drop index site_visit from work.visits;
quit;
The PROC SQL documentation adds that you cannot instruct PROC SQL to use an index: it decides whether the index is efficient. It also notes that CREATE INDEX cannot be used on a table accessed through an engine that does not support UPDATE processing. The data table below compares the three methods side by side.
How to read the output
Check that SAS used the index: MSGLEVEL=I
SAS does not announce index use by default. Set options msglevel=i; (the default is N) before the step. The SAS Concepts page says that if an index is used, a log message displays the name of the index, and if an index exists but is not used, the messages suggest ways to encourage its use, such as sorting the data into index order or specifying more buffers.
options msglevel=i;
data work.site_a;
set work.visits;
where site='A';
run;
Read the log for the index name. If no index message appears, SAS scanned the data set sequentially, which is correct behaviour when it estimates the subset is too large for the index to help.
List which indexes a data set has
Run PROC CONTENTS, or use the CONTENTS statement in PROC DATASETS. Per SAS Concepts, the report gives the number and names of indexes for the data file and the names of the key variables. For a walk-through of that procedure and its other sections, see PROC CONTENTS in SAS.
Worked example from the documentation: why SAS picks one index
SAS Concepts describes a WHERE expression of the form class=12 and major='Biology' when separate simple indexes exist on CLASS and MAJOR. SAS uses only one index per WHERE expression, and it chooses MAJOR because that condition qualifies the smaller subset (6,000 observations against 42,000 for CLASS=12). The practical lesson is the same as the composite-index advice: the index on the more selective condition does the work, which is why the most discriminating variable belongs first in a composite index.
Troubleshooting SAS indexes
The index vanished after sorting
You can sort an indexed data set only by directing the output to a new data set, and the new data set is not automatically indexed. With the FORCE option, the index file is deleted. Deleting a data set also deletes its index file. If you need both a sort order and an index, sort first and then create the index on the sorted result. SAS Concepts says BY-group processing can follow index order without a PROC SORT, which is how an index can stand in for sorting before a MERGE with a BY statement.
The index exists but the log says it was not used
Check the size of the subset first: if the WHERE expression selects more than roughly a third of the observations, SAS generally does not use the index. Next check the documented limits: a NOMISS index is not used when the query could match missing values, a BY statement with DESCENDING or NOTSORTED does not use the index, KEY= cannot be combined with BY, and a WHERE statement cannot be used with KEY=. Indexes cannot be created on views.
UNIQUE index was not built
When UNIQUE is requested and duplicates exist, the index is not created. Find and resolve the duplicate key values, then request the index again.
Copying an indexed data set
Per SAS Concepts, PROC COPY recreates the index by default (INDEX=YES), and copying disk to disk recreates it, but copying disk to tape does not.
Free alternatives to SAS
If you cannot run SAS, the same idea, a separate lookup structure over key columns, exists in other tools. Each entry below is stated as its own documentation describes it.
| Tool | What the documentation says | Documentation |
|---|---|---|
| SAS Viya for Learners | Free for students and educators; includes a ready-made JupyterLab setup for using R and Python with SAS. | SAS academic programs page |
| R data.table | setkey physically sorts a table and marks it sorted; setindex stores a row ordering for chosen columns without reordering the table. | data.table setkey reference |
| SQLite | CREATE [UNIQUE] INDEX name ON table (column, …) builds single- or multi-column indexes; UNIQUE disallows duplicate entries. | SQLite CREATE INDEX |
These tools differ from SAS in how an index is chosen at run time and in what it does to row order, so check each tool’s own documentation before porting a program. For other statistical-software guides on this site, see the research tools hub.
Sources
- SAS Institute: Understanding SAS Indexes (SAS Concepts), checked 2026-11-17
- SAS Institute: INDEX= Data Set Option, checked 2026-11-17
- SAS Institute: DATASETS Procedure (INDEX CREATE), checked 2026-11-17
- SAS Institute: PROC SQL CREATE INDEX statement, checked 2026-11-17
- SAS Institute: PROC SQL DROP statement, checked 2026-11-17
- SAS Institute: Software for students and educators, checked 2026-11-17
- data.table documentation: setkey and setindex, checked 2026-11-17
- SQLite: CREATE INDEX, checked 2026-11-17
Data: Ways to create an index on a SAS data set
Side-by-side comparison of the three documented ways to create an index on a SAS data set: the INDEX= data set option, PROC DATASETS INDEX CREATE and PROC SQL CREATE INDEX. Compiled from the SAS INDEX= data set option, DATASETS procedure and PROC SQL CREATE INDEX documentation pages read on 2026-11-17.
| Method | Applies to | Composite index syntax | Uniqueness option | Missing-value option | Naming rule for composite index |
|---|---|---|---|---|---|
| INDEX= data set option | Output data sets in DATA and PROC steps, at creation | index=(name=(var1 var2)) | /unique inside the specification | /nomiss inside the specification | Name given before the parenthesised variable list |
| PROC DATASETS INDEX CREATE | Existing data set named in a MODIFY statement | index create name=(var1 var2); at least two variables | UNIQUE option; index not created if duplicates exist | NOMISS option; index used only for WHERE processing | Valid SAS name; cannot match a variable name or another composite index name |
| PROC SQL CREATE INDEX | Existing table | create index name on table(col1, col2) | create unique index; changes producing duplicates are rejected | No NOMISS option listed on the CREATE INDEX page | Cannot match any column name in the table |
Sources
- SAS: INDEX= Data Set Option (accessed 2026-11-17)
- SAS: DATASETS Procedure (INDEX CREATE) (accessed 2026-11-17)
- SAS: PROC SQL CREATE INDEX statement (accessed 2026-11-17)
Compiled by CASRAI from the sources above. Free to reuse under CC BY 4.0 with attribution.







