Chapter Contents

Previous

Next

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
  • enables you to navigate within an interactive SAS session by using a new, point-and-click interface. This window replaces the ACCESS window, which was available in earlier releases of SAS software. For details about using this window, see your host companion or SAS Language Reference: Concepts.

• SAS/ACCESS LIBNAME statement for Relational Database Interfaces
  • enables you to assign a SAS libref directly to a relational DBMS (RDBMS) or to objects in an RDBMS, such as tables and views. You no longer need to create access and view descriptors, although all previously created access and view descriptors are fully supported in Version 7 and later.

  • enables you to use many new SAS/ACCESS LIBNAME options to specify how objects in your RDBMS data are processed by SAS.

  • enables you to customize how to connect to your RDBMS and how to control data locking.

• SAS/ACCESS data set options for Relational Database Interfaces
  • enable you to use many new SAS/ACCESS data set options to specify how your relational DBMS data are processed by SAS and how to control data locking.

  • enable you to use several base SAS data set options on data sets that access RDBMS data.

• Enhanced naming conventions and support
  • enable you to use 32-byte names for SAS data set and variable names and for relational DBMS table and column names when you use the new SAS/ACCESS LIBNAME statement.

  • enable you to store character data in SAS software up to 32,767 bytes.

  • enable you to use mixed case and nonstandard names that are not supported in Version 6.

• Performance optimization
  • passes joins directly to the relational DBMS for processing.

  • makes better use of RDBMS indexes.

  • provides enhanced interrupt- and error-handling.

• New macro variables
  • enable you to use two new macro variables, SYSDBRC and SYSDBMSG, for reporting RDBMS-specific errors.

• Relational DBMS support in PROC SQL
  • enables you to access RDBMS data directly by using PROC SQL.

  • enables you to update and delete RDBMS data directly through SQL or through a simple PROC SQL view that references the underlying data.

  • enables you to join RDBMS data so that the RDBMS processes the join more efficiently.

  • enables you to use the new DBCONDITION= data set option to subset RDBMS data within a PROC SQL view so that the RDBMS processes the subsetting conditions.

  • enables you to store embedded LIBNAME statements in PROC SQL views by using the new using libname syntax.

• The PROC windowing interface is not supported in Version 7 and later
  • The Access and View Descriptor windows for creating and editing descriptors are not supported in Version 7 and later. If the ACCESS procedure was available in Version 6 for your relational DBMS, then you can use ACCESS procedure statements to perform these tasks. For information about how to look at or update the contents of your descriptors, see Reviewing Variables or UPDATE.

    Similarly, the DBLOAD windows are no longer supported. If the DBLOAD procedure was available in Version 6 for your RDBMS, then you can use DBLOAD procedure statements to perform the same tasks.

  • The UPDATE statement has been added to the ACCESS procedure so that you can update descriptor files.

• Full Version 6 compatibility for existing SAS programs that use descriptors, the ACCESS procedure, the DBLOAD procedure, and the SQL Procedure Pass-Through Facility.

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 LIBNAME Statement

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.  

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

Previous

Next

Top of Page

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