Skip to main content
v2026.11,986 entries · CC-BY 4.0

Indexing in SAS: Create and Use Data Set Indexes

How indexing in SAS works: create simple or composite data set indexes three ways, check use with MSGLEVEL=I, and avoid common index pitfalls.

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

  1. Write the DATA step that builds the new data set.
  2. 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.
  3. Put UNIQUE or NOMISS after a slash inside the specification, and name a composite index by writing name=(var1 var2).
  4. 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

  1. Start proc datasets library=work;.
  2. Name the data set in a modify statement. The documentation says INDEX CREATE must be in a MODIFY run group.
  3. Write index create followed by a variable name for a simple index, or name=(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.
  4. Optionally add NOMISS, UNIQUE or UPDATECENTILES= as described on the INDEX CREATE page, then finish with run; and quit;.
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

  1. Open proc sql;.
  2. Write create index (or create unique index), the index name, then on the table name with the columns in parentheses, separated by commas.
  3. 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.
  4. To remove an index, use drop index with the index name and from the 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

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.

Ways to create an index on a SAS data set
MethodApplies toComposite index syntaxUniqueness optionMissing-value optionNaming rule for composite index
INDEX= data set optionOutput data sets in DATA and PROC steps, at creationindex=(name=(var1 var2))/unique inside the specification/nomiss inside the specificationName given before the parenthesised variable list
PROC DATASETS INDEX CREATEExisting data set named in a MODIFY statementindex create name=(var1 var2); at least two variablesUNIQUE option; index not created if duplicates existNOMISS option; index used only for WHERE processingValid SAS name; cannot match a variable name or another composite index name
PROC SQL CREATE INDEXExisting tablecreate index name on table(col1, col2)create unique index; changes producing duplicates are rejectedNo NOMISS option listed on the CREATE INDEX pageCannot match any column name in the table

Sources

Compiled by CASRAI from the sources above. Free to reuse under CC BY 4.0 with attribution.

Follow CASRAI

Research-administration guidance, standards updates and independent tool reviews.

Ask CASRAI · free to try

Ask about Indexing in SAS: Create and Use Data Set Indexes

2 free questions a day, no account.

An AI assistant for research: funder rules, compliance, methods and publishing. It links the sources behind its answers and labels answers that come from a web search.

Answers draw on CASRAI's guides and dictionary plus the federal and funder documents we index: Federal Register, Grants.gov, Regulations.gov and UKRI.

Works on this site and inside Claude, Cursor and the AI tools you already use.

Everything CASRAI publishes — this page, the dictionary, the guides and the news — stays open to read, with no account and no card.

Referenced across the research world

University of Cambridge logoColumbia University logoCrossref logoUniversity of Edinburgh logoHarvard University logoUniversity of Oxford logoPrinceton University logoStanford School of Medicine logoUniversity College London logoORCID logoUniversity of Cambridge logoColumbia University logoCrossref logoUniversity of Edinburgh logoHarvard University logoUniversity of Oxford logoPrinceton University logoStanford School of Medicine logoUniversity College London logoORCID logo
  • University of Cambridge logo
  • Columbia University logo
  • Crossref logo
  • University of Edinburgh logo
  • Harvard University logo
  • University of Oxford logo
  • Princeton University logo
  • Stanford School of Medicine logo
  • University College London logo
  • ORCID logo

View CASRAI adoption →