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.
Note: 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;
Table 6.1 SAS Log
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.