Introduction When processing a query that contains a subsetting WHERE clause or that joins multiple tables, PROC SQL must locate specific rows in the referenced table(s). Creating an index for a table enables PROC SQL, in certain circumstances, to locate specific rows more quickly and efficiently. An index is an auxiliary file that stores the physical location of values for one or more specified columns (key columns) in a table. In an index, each unique value of the key column(s) is paired with a location identifier for the row that contains that value. In the same way that you use a book's subject index to find a page that discusses a particular subject, PROC SQL uses the system of directions in an index to access specific rows in the table directly, by index value. You can create more than one index for a single table. All indexes for a SAS table are stored in one index file.

You cannot create an index on a view.

The following PROC SQL step uses the CREATE INDEX statement to create an index for a table, and uses the DESCRIBE TABLE statement to display information about the index, along with other information about the table, in the SAS log: proc sql; create unique index empid on work.payrollmaster(empid); describe table work.payrollmaster;

create table WORK.PAYROLLMASTER( bufsize=4096 )
  (
   DateOfBirth num format=DATE9. informat=DATE9.,
   DateOfHire num format=DATE9. informat=DATE9.,
   EmpID char(4),
   Gender char(1),
   JobCode char(3),
   Salary num format=DOLLAR9.
  );
create unique index EmpID on WORK.PAYROLLMASTER(EmpID);

 

Time to Complete This lesson contains pages and takes approximately 30 minutes to complete.

Objectives In this lesson, you learn to determine when it is appropriate to create an index create simple and composite indexes on a table create an index that ensures that values of the key column(s) are unique control whether PROC SQL uses an index or which index it uses display information about the structure of an index in the SAS log drop (delete) an index from a table.

Prerequisites Before taking this lesson, you should complete the following lessons: .