Selecting a SAS/ACCESS Method

In SAS/ACCESS, you can often complete a task in several ways. For example, you can access DBMS tables and views by using the LIBNAME statement or the Pass-Through Facility. Before processing complex or data-intensive operations, you might want to test several methods first to determine the most efficient one for your particular task.

You should use the SAS/ACCESS LIBNAME statement for the fastest and most direct method of accessing your DBMS data except when you need to use SQL that is not ANSI-standard. ANSI-standard SQL is required when you use the SAS/ACCESS library engine in the SQL procedure. However, the Pass-Through Facility accepts all SQL extensions that your DBMS provides.

Here are the advantages of using the SAS/ACCESS LIBNAME statement.

• 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, lets you specify how data is processed, and lets you easily view your DBMS tables in SAS.

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

• The LIBNAME statement gives you 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 processing of joins and WHERE clauses by passing them directly to the DBMS, which takes advantage of the indexing and other processing capabilities of your DBMS. For more information, see Overview of Optimizing Your SQL Usage.

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

Here are the advantages of using the Pass-Through Facility.

• You can use Pass-Through Facility statements so the DBMS can 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 let the DBMS optimize queries when queries have summary functions (such as AVG and COUNT), GROUP BY clauses, or columns that expressions create (such as the COMPUTED function). The DBMS optimizer can use indexes on DBMS columns to process queries more rapidly.

• On some DBMSs, you can use Pass-Through Facility statements with SAS/AF applications to handle transaction processing of 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 extensions to ANSI SQL that your DBMS provides.

Here is a list of tasks and the features that you can use to accomplish them.

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