SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition

When you query DICTIONARY.Tables or Sashelp.Vtable, all of the tables and views in

all libraries that are assigned to the SAS session are opened to retrieve the requested

information.

You can use a WHERE clause to help restrict which libraries are searched. However, the

WHERE clause does not process most function calls such as UPCASE.

For example, if where UPCASE (libname) ='WORK' is used, the UPCASE

function prevents the WHERE clause from optimizing this condition. All libraries that

are assigned to the SAS session are searched. Searching all of the libraries could cause

an unexpected increase in search time, depending on the number of libraries that are

assigned to the SAS session.

All librefs and SAS table names are stored in uppercase. If you supply values for

LIBNAME and MEMNAME in uppercase and you remove the UPCASE function, the

WHERE clause will be optimized and performance will be improved. In the previous

example, the code would be changed to where libname='WORK'.

Note: Searching all librefs might cause unexpected results. If all librefs are searched, a

view might exist that contains a libref that is not currently assigned to the SAS

session. When this view is opened to retrieve information for the query, an error

occurs.

Note: If you query table information from a library that is assigned to an external

database, and you use the LIBNAME statement PRESERVE_TAB_NAMES=YES

option or the PRESERVE_COL_NAMES=YES option, and you provide the table or

column name as it appears in the database, you do not need to use the UPCASE

function.

Using SAS Data Set Options with PROC SQL

In PROC SQL, you can apply most of the SAS data set options, such as KEEP= and

DROP=, to tables or SAS/ACCESS views anytime you specify a table or SAS/ACCESS

view. In the SQL procedure, SAS data set options that are separated by spaces are

enclosed in parentheses. The data set options immediately follow the table or

SAS/ACCESS view name. In the following PROC SQL step, the RENAME= data set

option renames LNAME to LASTNAME for the Staff1 table. The OBS= data set option

restricts the number of rows that are read from Staff1 to 15:

proc sql;

create table

staff1(rename=(lname=lastname)) as

select *

from staff(obs=15);

SAS data set options can be combined with SQL statement arguments. In the following

PROC SQL step, the PW= data set option assigns a password to the Test table, and the

ALTER= data set option assigns an ALTER password to the Staff1 table:

proc sql;

create table test

(a character, b numeric, pw=cat);

create index staffidx on

staff1 (lastname, alter=dog);

In this PROC SQL step, the PW= data set option assigns a password to the ONE table.

The password is used when inserting a row and updating the table.

156 Chapter 5 • Programming with the SQL Procedure