Indexes and Views in PROC SQL

Question 1

What is an index?

Answer 1

An index allows you to access observations directly. The index stores values in ascending value order for a specific variable or variables and includes information as to the location of those values within observations in the data file.

For example, suppose you want the observation with SSN (Social Security number) equal to 123-45-6789:

Without an index, SAS accesses observations sequentially in the order in which they are stored in the data file. SAS reads each observation, looking for SSN=123-45-6789 until all observations are read.

With an index on variable SSN, SAS accesses the observation directly. SAS satisfies the condition using the index and goes straight to the observation that contains the value without having to read each observation.

Question 2

What are the two types of indexes?

Answer 2

• Simple: based on only one column
• Composite: based on two or more columns. In the index, the values of the key columns are concatenated to form a single value. Needs a new name that is not the name of any existing columns.

Question 3

What are 4 cases where creating an index would improve efficiency?

Answer 3

1. a WHERE clause that contains a comparison operator, TRIM or SUBSTR, CONTAINS, or LIKE
2. a subquery returning values to the IN operator
3. a correlated subquery in which the column being compared with the correlated reference is indexed
4. a join in which the join expression contains the = operator, or a join in which all the columns in the join expression are indexed in one of the tables being joined.

pg. 229-230

Question 4

3 benefits of using an index

Answer 4

1. A small subset of data (<15%) can be accessed more quickly (The bigger the subset, the less advantageous it is to use an index)
2. equijoins can be performed without internal sorts
3. uniqueness can be enforced by creating a unique index

Question 5

4 costs of using an index

Answer 5

1. Needs more CPU time
2. Needs additional input/output requests when compared to reading the table sequentially
3. Uses additional memory for buffers into which the index pages and code are loaded for processing
4. Requires additional disk space

Question 6

Create index syntax

Answer 6

Unique specifies that all the values in the column are unique

proc sql;

create (unique) index index-name

on table1 (columns);

Question 7

How do you find out whether a table has any indexes?

Answer 7

Use DESCRIBE TABLE

Question 8

How do you find out whether SAS is actually using the index you created?

Answer 8

Note the default option = N

Use OPTIONS MSGLEVEL = I before PROC SQL

It will tell you about index usage

Question 9

What are the two options to control index usage and what do they do?

Answer 9

IDXWHERE= and IDXNAME = in parenthesis in the from clause after the table name

IDXWHERE = YES : tells SAS to choose the best index to optimize a where expression, and to disregard the possibility that a sequential search of the table might be more resource-efficient

IDXWHERE = NO : tells SAS to ignore all indexes and satisfy the conditions of a where expression with sequential search of the table

IDXNAME = index name: tells SAS to use an index that you specify

Question 10

How do you delete an index?

Answer 10

DROP INDEX statement

proc sql;

drop index index-name

from tablename;

Question 11

Can an index be created on a view?

Answer 11

NO

Question 12

What is a VIEW?

Answer 12

A PROC SQL view is a stored query expression that reads data values from its underlying files. The view contains only the logic for accessing the data, not the data itself.

Question 13

Create a view syntax

Answer 13

The rest is normal proc sql query

proc sql;

create view viewname as

select ….

;

Question 14

Get information about a view

Answer 14

proc sql;

describe view viewname;

Question 15

How do you delete a view?

Answer 15

proc sql;

drop view view-name;

Question 16

When you specify SQL options how long do they stay?

Answer 16

It remains in effect until you change it or you re-invoke PROC SQL.

Question 17

Name 2 SQL options to control execution and what they do

Answer 17

proc sql inobs = number;

proc sql outobs = number;

INOBS = restrict the number of input rows (rows that are read)

OUTOBS = restrict the number of output rows

Question 18

Name 3 SQL options to control output and what they do

Answer 18

Double-spaces the output. Default is NODOUBLE

proc sql double/nodouble;

proc sql flow/noflow/flow=n/flow=nm;

proc sql number/nonumber;

Question 19

Name a SQL option used for evaluating performance and what it does

Answer 19

Specify whether PROC SQL writes timing information for each statement to the SAS log. NOSTIMER is default.

proc sql stimer/nostimer;

Question 20

What can you use to add, drop, or change PROC SQL options without re-invoking the SQL procedure?

Answer 20

RESET options;

Question 21

What is a Dictionary Table?

Answer 21

Read-only SAS tables that have info about SAS libraries, SAS macros, SAS system options, and titles and footnotes currently in effect.

You use it like any other SAS table. The libref is Dictionary.

Question 22

Write the code to list all the tables in the Sasuser library that contain a column naed EmpID

Answer 22

proc sql;

select memname

from dictionary.columns

where libname = ‘SASUSER’

and name = ‘EmpID’;