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.

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);

In this chapter, you learn to create and manage various types of indexes with PROC SQL.