24 Extracting DBMS Data to a SAS Data Set Chapter 3
dynamic, interactive manner. This is demonstrated in the following statement, which
causes a dialog box to appear and prompt the user to enter connection information,
such as a username and password:
libname myoralib oracle dbprompt=yes defer=no;
The dialog box that appears contains the DBMS connection options that are valid for
the SAS/ACCESS engine that is being used, in this case, Oracle.
Using the DBPROMPT= option on the LIBNAME statement offers several
advantages. DBMS account passwords are protected because they do not need to be
stored in a SAS program or descriptor file. Also, when a password or username
changes, the SAS program does not need to be modified. Another advantage is that the
same SAS program can be used by any valid username and password combination that
is specified during execution. You can also use connection options in this interactive
manner when you want to run a program on a production server instead of testing a
server without making modifications to your code. By using the prompt window, the
new server name can be specified dynamically.
Note: The DBPROMPT= option is not available in the SAS/ACCESS interface to
DB2 under OS/390.
Extracting DBMS Data to a SAS Data Set
If you are the owner of a DBMS table and do not want anyone else to read the data,
you might want to extract the data (or a subset of the data) and not distribute
information about either the access descriptor or view descriptor.
Note: You may need to take additional steps to restrict LIBNAME or Pass-Through
access to the extracted data set.
If you extract data from a view that has a SAS password assigned to it, the new SAS
data file is automatically assigned the same password. If a view does not have a
password, you can assign a password to the extracted SAS data file by using the
MODIFY statement in the DATASETS procedure. See the Base SAS Procedures Guide
for more information.
Defining Views and Schemas
If you want to provide access to some but not all fields in a DBMS table, you can
create a SAS view that prohibits access to the sensitive data by specifying that
particular columns be dropped. Columns that are dropped from views do not affect the
underlying DBMS table and can be reselected for later use.
Some SAS/ACCESS engines support LIBNAME options that restrict or qualify the
scope, or schema, of the tables in the libref. For example, the DB2 engine supports the
AUTHID= and LOCATION= options, and the Oracle engine supports the SCHEMA=
and DBLINK= options. See the SAS/ACCESS documentation for your DBMS to
determine which options are available to you.
The following example uses the SAS/ACCESS interface to Oracle:
libname myoralib oracle user=testuser password=testpass
path=’myoraserver’ schema=testgroup;
proc datasets lib=myoralib;
run;
In this example, the MYORALIB libref is associated with the Oracle schema named
TESTGROUP. The DATASETS procedure lists only the tables and views that are
