This document explains how to create and drop indexes in SAS© PROC SQL. It covers the creation of simple indexes (on a single column) and composite indexes (on multiple columns), as well as the use of the UNIQUE keyword to ensure the uniqueness of indexed values. Advice is provided for efficient index creation, including minimizing the number of indexes and using them for queries targeting small subsets of data. Limitations and performance considerations are also discussed.
Data Analysis
Type : CREATION_INTERNE
Examples use tables created via 'CREATE TABLE ... LIKE' from an existing table (COUNTRIES), assumed to be available or internally created for the purpose of the example.
1 Code Block
PROC SQL Data
Explanation :
This example first creates a new table named `newcountries` using the structure of the `countries` table. Then, it creates a simple index named `area` on the `area` column of the `newcountries` table. A simple index is applied to a single column, and its name must be identical to that of the indexed column.
This example first creates a new table named `newcountries` using the structure of the `countries` table. Then, it creates a simple index named `area` on the `area` column of the `newcountries` table. A simple index is applied to a single column, and its name must be identical to that of the indexed column.
Copied!
| 1 | PROC SQL; |
| 2 | create TABLE newcountries |
| 3 | like countries; |
| 4 | create index area |
| 5 | on newcountries(area); |
| 6 | QUIT; |
2 Code Block
PROC SQL
Explanation :
This example creates a composite index named `places` on the `name` and `continent` columns of the `newcountries` table. A composite index applies to two or more columns. The name of the composite index cannot be the same as the name of any of the table's columns.
This example creates a composite index named `places` on the `name` and `continent` columns of the `newcountries` table. A composite index applies to two or more columns. The name of the composite index cannot be the same as the name of any of the table's columns.
Copied!
| 1 | PROC SQL; |
| 2 | create index places |
| 3 | on newcountries(name, continent); |
| 4 | QUIT; |
3 Code Block
PROC SQL
Explanation :
This example recreates the `newcountries` table (if it doesn't exist) and then creates a unique composite index named `places` on the `name` and `continent` columns. The use of the `UNIQUE` keyword ensures that each combination of values in the `name` and `continent` columns of the table is unique. Any attempt to modify the table in a way that would result in non-unique index values will be rejected by SAS.
This example recreates the `newcountries` table (if it doesn't exist) and then creates a unique composite index named `places` on the `name` and `continent` columns. The use of the `UNIQUE` keyword ensures that each combination of values in the `name` and `continent` columns of the table is unique. Any attempt to modify the table in a way that would result in non-unique index values will be rejected by SAS.
Copied!
| 1 | PROC SQL; |
| 2 | create TABLE newcountries |
| 3 | like countries; |
| 4 | create unique index places |
| 5 | on newcountries(name, continent); |
| 6 | QUIT; |
4 Code Block
PROC SQL
Explanation :
This example drops the index named `places` from the `newcountries` table using the `DROP INDEX` statement. This is useful for freeing up disk space or for adjusting indexing strategies to improve performance.
This example drops the index named `places` from the `newcountries` table using the `DROP INDEX` statement. This is useful for freeing up disk space or for adjusting indexing strategies to improve performance.
Copied!
| 1 | |
| 2 | PROC SQL; |
| 3 | drop index places from newcountries; |
| 4 | QUIT; |
| 5 |
This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.
Copyright Info : Copyright © SAS Institute Inc. All Rights Reserved
