SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition

Output 5.7 PROC REPORT Output Showing the First Occurrence Only of Each State Value

Accessing a DBMS with SAS/ACCESS Software

Overview of Accessing a DBMS with SAS/ACCESS Software

SAS/ACCESS software for relational databases provides an interface between SAS

software and data in other vendors' database management systems (DBMSs).

SAS/ACCESS software provides dynamic access to DBMS data through the

Accessing a DBMS with SAS/ACCESS Software 167

SAS/ACCESS LIBNAME statement and the PROC SQL pass-through facility. The

LIBNAME statement enables you to assign SAS librefs to DBMS objects such as

schemas and databases. The pass-through facility enables you to interact with a DBMS

by using its SQL syntax without leaving your SAS session.

It is recommended that you use the SAS/ACCESS LIBNAME statement to access your

DBMS data because it is usually the fastest and most direct method of accessing DBMS

data. The LIBNAME statement offers the following advantages:

• Significantly fewer lines of SAS code are required to perform operations in 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

browse your DBMS tables in SAS.

• You do not need to know your DBMS's SQL language to access and manipulate your

DBMS data. 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 the indexing and

other processing capabilities of your DBMS.

An exception to this recommendation occurs when you need to use SQL that does not

conform to the ANSI standard. The SAS/ACCESS LIBNAME statement accepts only

ANSI standard for SQL, but the PROC SQL pass-through facility accepts all the

extensions to SQL that are provided by your DBMS. Another advantage of this access

method is that 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 that were created by expressions (such as the COMPUTED

function).

For more information about SAS/ACCESS software, see SAS/ACCESS for Relational

Databases: Reference.

Connecting to a DBMS By Using the LIBNAME Statement

Overview of Connecting to a DBMS By Using the LIBNAME

Statement

Use the LIBNAME statement to read from and write to a DBMS object as if it were a

SAS data set. After connecting to a DBMS table or view using the LIBNAME statement,

you can use PROC SQL to interact with the DBMS data.

For many DBMSs, you can directly access DBMS data by assigning a libref to the

DBMS using the SAS/ACCESS LIBNAME statement. Once you have associated a

libref with the DBMS, you can specify a DBMS table in a two-level SAS name and

work with the table like it is a SAS data set. You can also embed the LIBNAME

statement in a PROC SQL view. For more information, see the “CREATE VIEW

Statement” on page 243.

PROC SQL takes advantage of the capabilities of a DBMS by passing it certain

operations whenever possible. For example, before implementing a join, PROC SQL

checks to determine whether the DBMS can perform the join. If it can, then PROC SQL

passes the join to the DBMS, which enhances performance by reducing data movement

168 Chapter 5 • Programming with the SQL Procedure