O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required