Chapter Contents

Previous

Next

Comparing SAS/ACCESS Features

It is recommended that you use the new SAS/ACCESS LIBNAME statement to access your DBMS data easily and transparently. However, if your SAS/ACCESS interface does not support the LIBNAME statement, you might need to choose between the SQL Procedure Pass-Through Facility or view descriptors.

SAS/ACCESS LIBNAME Statement

When you use the SAS/ACCESS LIBNAME statement, the LIBNAME engine sends and receives data between the DBMS and the SAS System.

Beginning in Version 7, the LIBNAME statement is usually the fastest and most direct method of accessing DBMS data. Here are some of the tasks it performs:

• The LIBNAME statement provides transparent access to DBMS data. Once a libref is associated with a group of DBMS tables or views, you can use this libref just as you would use any SAS libref.

• You can use member and variable names of up to 32 characters.

• Automatic conversion of SAS to DBMS data types, and DBMS to SAS data types, allows you to move data easily between SAS and your DBMS. You can use SAS/ACCESS LIBNAME and data set options, including the DBTYPE= data set option, to further control the data conversion process.

• For extra security, you can prompt the user for DBMS connection information at runtime by using the DBPROMPT= libname and data set options. Locally stored environment variables can also be used from a SAS client to establish a connection to a DBMS.

The SAS/ACCESS LIBNAME statement has the following advantages over the SQL Procedure Pass-Through Facility and Version 6 view descriptors:

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

• You do not need to know your DBMS's SQL language 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 normal SAS syntax.

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

• The LIBNAME engine optimizes the processing of joins and WHERE clauses by passing these operations directly to the DBMS to take advantage of your DBMS's indexing and other processing capabilities. See SAS/ACCESS LIBNAME Statement for more information.

SQL Procedure Pass-Through Facility and View Descriptors

If you cannot use the LIBNAME statement, you can also use either the SQL Procedure Pass-Through Facility or SAS/ACCESS view descriptors to access DBMS data. There are certain cases where it is more efficient to use one method or the other, and choosing the best method can enhance your program's performance.

In both methods, the SAS/ACCESS interface view engine sends and receives data between the DBMS and the SAS System.

The SQL Procedure Pass-Through Facility and SAS/ACCESS view descriptors perform these tasks equally well:

• View descriptors and SQL Procedure Pass-Through Facility queries both offer direct access to DBMS data. SQL Procedure Pass-Through Facility queries can also be stored as PROC SQL views. When you refer to a view descriptor or a PROC SQL view (that is, a stored Pass-Through query) in a SAS program, the DBMS connection is automatically made, and the DBMS data is retrieved for your use.

• You can assign SAS variable names and formats to DBMS columns and data types by using either SAS/ACCESS descriptors or SQL Procedure Pass-Through Facility queries. You use the RENAME= and FORMAT= statements when you create a descriptor. When you create a SQL Procedure Pass-Through Facility query (or PROC SQL view), you can use column aliases or modifiers in the SELECT clause, or you can use a table alias and column list in the FROM clause.

• You can retrieve a subset of data from a DBMS table or view by using either view descriptors or SQL Procedure Pass-Through Facility queries. Both methods use a WHERE statement or clause. Both methods also enable you to group or sort the data that you retrieve by using GROUP BY and ORDER BY clauses.

• For extra security, you can assign SAS System passwords to either SAS/ACCESS descriptors or to SQL Procedure Pass-Through Facility queries that are stored as PROC SQL views.

There are some advantages to using either the SQL Procedure Pass-Through Facility or SAS/ACCESS descriptors. Use the following information to help you decide between the two methods:

• Advantages of the SQL Procedure Pass-Through Facility over view descriptors
  • You can use 32-character names in the SQL Pass-Through Facility; whereas, view descriptors are still limited to 8-character names.

  • SQL Procedure 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.

  • SQL Procedure 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.

  • You control the SELECT statement when you use the SQL Procedure Pass-Through Facility, whereas the interface view engine generates the SELECT statement in a view descriptor.

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

• Advantage of the SQL Pass-Through Facility over the SAS/ACCESS LIBNAME Statement
  • The SQL Procedure Pass-Through Facility accepts all the extensions to SQL provided by your DBMS; whereas, you can use only ANSI standard SQL in the LIBNAME statement.

• Advantages of SAS/ACCESS view descriptors over the SQL Pass-Through Facility
  • SAS WHERE and BY statements can be passed to the DBMS for processing. Inthe case of view descriptors vs. PROC SQL views, view descriptors enable you to pass additional WHERE clauses to the DBMS by specifying the WHERE clauses in the selection criteria or in your SAS program.

  • In the SAS/ACCESS interfaces to CA-OpenIngres, ORACLE, Oracle Rdb, and SYBASE, you can pass certain SAS data set options (such as KEEP, RENAME, and DROP) to the DBMS. You can also pass certain sorting requests with a BY statement or ORDER BY clause to the DBMS for processing. For more information about using a BY statement with a view descriptor, see Sorting DBMS Data.

Chapter Contents

Previous

Next

Top of Page

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