The Complete Guide to SAS Indexes by Michael A' Raithel Reviewed by Horst Wolter

1
The Complete Guide to SAS IndexesbyMichael A.
RaithelReviewed by Horst Wolter
2
About the Author

• Wrote two previous SAS books and over twenty SAS
  technical papers.
• Section Chair at SUGI, SESUG and NESUG and
  co-chaired NESUG in 1995.
• First book entitled Tuning SAS Applications in
  the MVS Environment resides in the Smithsonian
  Institution of American Historys Permanent
  Research Collection of Information Technology.

3
Goals of Indexing

• The main goal of using a SAS index is to read
  only a small portion of a large SAS dataset
  instead of reading the entire SAS dataset.
• Reduce I/Os.
• Reduce wall clock time.
• Reduce CPU time.

4
When to Index

• Consider
• the size of the subsets (the overhead of using an
  index can become greater than the overhead of a
  sequential read of the entire dataset)
• frequency of use (is building an index cost
  effective)
• variability of the data (indexed SAS datasets
  have an overhead to maintain if data changes
  often and may not be cost effective)

5
Indexing Guidelines (Table 2.1 from book)
Indexing Action
Subset Size
6
Index Variable Selection

• What variables to consider for an index?
• Often used to subset data
• Values that represent a small subset
• A SAS dataset sorted by the index variable is
  more efficient

7
How to create an Index

• Data Step Method
• DATA data-set-name(INDEX(index-name(var1 var2
  etc.) lt/UNIQUEgt lt/NOMISSgt))
• SQL Method
• proc sql
• CREATE ltUNIQUEgt INDEX index-name ON table-name (
  column lt, ... columngt)
• quit

8
Viewing Index Information

• proc contents datadata-set-name ltcentilesgt
• run
• Indexes 1
• Alphabetic List of Indexes and Attributes
• 
  of
• 
  Unique
• Index
  Values
• 1 var1
  1535958

9
Using Indexes

• With a WHERE clause
• With a BY statement
• SAS determines whether or not to use an index
  based on a number of rules as described in the
  book.

10
Other Topics covered in the book

• Composite indexes
• Creating indexes while using PROC SORT, PROC
  DATASETS
• Creating Multiple Indexes
• Rules for SAS Using an Index
• Removing Indexes
• Index options e.g. Unique Option, NoMiss Option
• Recovering Missing Index Files
• Repairing Damaged Index Files
• And more

11
From SAS Press
12
Questions?