Chapter 6: Chapter 6: Creating And Managing Indexes Using Proc SQL

proc sql; create simple index(empid) on sasuser.staffmaster;

proc sql; create empid index on sasuser.staffmaster(empid);

proc sql; create simple index on empid from sasuser.staffmaster;

proc sql; create index empid on sasuser.staffmaster(empid);

KEY

UNIQUE

NODUPS

NODUPKEY

proc sql; create index destination on sasuser.flightdelays(flightnumber, destination);

proc sql; create composite index places on sasuser.flightdelays (flightnumber, destination);

proc sql; create index on flightnumber,destination from sasuser.flightdelays (places);

proc sql; create index places on sasuser.flightdelays (flightnumber, destination);

options msglevel=i; proc sql; select * from sasuser.internationalflights where date between '01mar2000'd and '07mar2000'd;

options index=yes; proc sql; select * from sasuser.internationalflights where date between '01mar2000'd and '07mar2000'd;

proc sql; select * (idxwhere=yes) from sasuser.internationalflights where date between '01mar2000'd and '07mar2000'd;

proc sql; select * (msglevel=i) from sasuser.internationalflights where date between '01mar2000'd and '07mar2000'd;

proc sql; drop composite index flights from sasuser.marchflights;

proc sql; delete index flights on sasuser.staffmaster(flightnumber, date);

proc sql; drop index flights from sasuser.marchflights;

proc sql; delete index on sasuser.marchflights(flightnumber, flightdate);

DESCRIBE INDEX

DESCRIBE TABLE

SELECT

IDXNAME

It forces SAS to use the best available index to process the WHERE expression.

It creates an index from the expression in the WHERE clause.

It writes messages about index usage to the SAS log.

It stops SAS from using any index.

Equijoins can be performed without internal sorts.

Indexes provide fast access to a small subset of data.

Indexes can be created for numeric columns only.

Indexes can enforce uniqueness.

The query contains an IN subquery that references the key column.

The key column is specified in a WHERE clause expression that contains a comparison operator, the TRIM or SUBSTR function, the CONTAINS operator, or the LIKE operator.

The query is an equijoin, and all the columns in the join expression are indexed in one of the tables being joined.

The key column is specified only in a SELECT clause.

The specified index must exist.

The specified index must be suitable by having at least its first or only column match a condition in the WHERE expression.

The option allows you to create and name an index on the table.

The option directs SAS to use an index that you specify.