Selecting a SAS/ACCESS Method

In SAS/ACCESS, there are often several ways to complete a task. For example, you can access DBMS tables and views by using the LIBNAME statement or the Pass-Through Facility. The advantages and limitations of these features are described below. Before processing complex or data-intensive operations, you might want to test several of these features to determine the most efficient feature for your particular task.

It is generally recommended that you use the SAS/ACCESS LIBNAME statement to access your DBMS data because this is usually the fastest and most direct method. An exception to this is when you need to use non-ANSI standard SQL. ANSI standard SQL is required when you use the SAS/ACCESS library engine in the SQL procedure. The Pass-Through Facility, however, accepts all the extensions to SQL that are provided by your DBMS.

The SAS/ACCESS LIBNAME statement has the following advantages:

• Significantly fewer lines of SAS code are required to perform operations on your DBMS. For example, a single LIBNAME statement establishes a connection to your DBMS, enables you to specify how your data is processed, and enables you to easily view your DBMS tables in SAS.

• You do not need to know the SQL language of your DBMS in order to access and manipulate data on your DBMS. You can use SAS procedures, such as PROC SQL, or DATA step programming on any libref that references DBMS data. You can read, insert, update, delete, and append data, as well as create and drop DBMS tables by using SAS syntax.

• The LIBNAME statement provides more control over DBMS operations such as locking, spooling, and data type conversion through the use of LIBNAME and data set options.

• The engine can optimize the processing of joins and WHERE clauses by passing these operations directly to the DBMS. This takes advantage of your DBMS's indexing and other processing capabilities. For more information, see Overview of Optimizing Your SQL Usage.

• The engine can pass some functions directly to the DBMS for processing.

The Pass-Through Facility has the following advantages:

• Pass-Through Facility statements enable the DBMS to optimize queries, particularly when you join tables. The DBMS optimizer can take advantage of indexes on DBMS columns to process a query more quickly and efficiently.

• Pass-Through Facility statements enable the DBMS to optimize queries when the queries have summary functions (such as AVG and COUNT), GROUP BY clauses, or columns created by expressions (such as the COMPUTED function). The DBMS optimizer can use indexes on DBMS columns to process the queries more quickly.

• On some DBMSs, you can use Pass-Through Facility statements with SAS/AF applications to handle the transaction processing of the DBMS data. Using a SAS/AF application gives you complete control of COMMIT and ROLLBACK transactions. Pass-Through Facility statements give you better access to DBMS return codes.

• The Pass-Through Facility accepts all the extensions to ANSI SQL that are provided by your DBMS.

The following table contains a list of tasks and the features that you can use to accomplish them.

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