Chapter Contents


SAS/ACCESS Software for Relational Databases: Reference

SAS/ACCESS Changes and Enhancements for Version 7 and Version 8

SAS/ACCESS software provides an interface between the SAS System and relational database management systems (DBMSs). In Version 6, the SAS/ACCESS interface consisted of one or more of the following: the ACCESS and DBLOAD procedures, a SAS/ACCESS interface view engine, and the SQL Procedure Pass-Through Facility. These features continue to be supported in Versions 7 and 8, in addition to the following new features that are available in Version 7 and later:

SAS Explorer Window

The new graphical interface to the SAS System, the SAS Explorer window, enables you to manage your SAS files. For example, you can display your SAS data libraries, select one, and open a SAS data set or view with the Viewtable. In Version 7 and later, you use the Explorer window instead of the ACCESS window, which was available in earlier releses of SAS software. For details about using this window, see your host companion or the SAS Language Reference: Concepts.


SAS/ACCESS engines now provide direct and dynamic access to your relational DBMS data without using access descriptors or view descriptors. You can now assign a SAS libref directly to an RDBMS and then use this libref to identify a table or view in that DBMS. This libref.dbms_table behaves like a SAS data set and, therefore, can be used in the DATA step and in SAS procedures just like a SAS data set. You can read, update, insert, or delete RDBMS data, as well as create SAS data sets from or SAS data views on the RDBMS data. In addition, you can use many new options in the LIBNAME statement to specify how to connect to your RDBMS and how to access and process your RDBMS data.

SAS/ACCESS Data Set Options

Because the new SAS/ACCESS LIBNAME statement enables you to access relational DBMS tables and views through a SAS libref, you can use them as data sets. You can use many new SAS/ACCESS data set options, as well as several existing SAS data set options, to specify how your data set is processed by SAS. The SAS/ACCESS data set options provide greater flexibility and control over row and table locking, buffering data, indexing, the use of DBMS data types, and other RDBMS functionality.

Note:   Control over locking and buffering might not be available for your RDBMS. See your RDBMS chapter for details.  [cautionend]

Enhanced Naming Conventions and Support

SAS software and SAS/ACCESS software now support the following names up to 32 bytes: SAS data set, SAS variable, relational DBMS table name, and RDBMS column name. You can also use mixed case and special characters when you specify a SAS or RDBMS even if the name does not conform to SAS naming conventions. See SAS/ACCESS LIBNAME and PROC SQL Options and your RDBMS chapter for details about the availability of each of these options. The SQL Procedure Pass-Through Facility fully supports long names as well.

Performance Optimization

SAS/ACCESS provides several enhancements in the areas of indexing, error-handling, support of relational DBMS objects such as triggers and stored procedures, and passing joins to the RDBMS. Whenever possible, SAS software now optimizes queries to use indexes. You can also use SAS/ACCESS LIBNAME and data set options to take advantage of RDBMS indexes.

In addition, you can list your RBMS tables by using the SAS Explorer window or PROC DATASETS, and you can list the SAS metadata for each RDBMS table or view by using the Explorer window or PROC CONTENTS.

New Macro Variables

Two new macro variables, SYSDBMSG and SYSDBRC, provide relational DBMS error message and return code information. These variables are similar to the SQLXMSG and SQLXRC macro variables that are available in the SQL Procedure Pass-Through Facility.

Relational DBMS Support in the SQL Procedure

The SQL procedure has several enhancements in Version 7 and later, and many of the enhancements interact with SAS/ACCESS software. For a complete description of PROC SQL, see the SAS Procedures Guide.

After you assign a libref to a relational DBMS, you can reference this new data set in a PROC SQL statement in order to access, update, or delete RDBMS data. You can also embed LIBNAME information in a PROC SQL view; therefore, every time the PROC SQL view is processed, you automatically connect to the RDBMS and you can access its data.

You can specify a simple PROC SQL view--that is, a view based on one table--in a PROC SQL UPDATE or DATA step UPDATE statement to update the view's underlying data.

Joins are passed to the RDBMS to process whenever possible. The RDBMS performs the join and returns only the results of the join to your SAS session. This provides a major performance enhancement for many of your SAS programs that perform joins.

Finally, you can still retrieve RDBMS data from or send RDBMS statements to an RDBMS through the Pass-Through facility.

ACCESS Procedure UPDATE Statement

You can now use the ACCESS procedure's UPDATE statement to update existing access descriptors and view descriptors that you have created by using the ACCESS procedure. You can use the LIST statement in PROC ACCESS to display the descriptor's content before you update it. UPDATE is provided because the ACCESS procedure windowing interface is not supported in Version 7 and later.

Chapter Contents



Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.