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
• 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
For more information about SAS/ACCESS software, see SAS/ACCESS for Relational
Connecting to a DBMS By Using the LIBNAME Statement
Overview of Connecting to a DBMS By Using the LIBNAME
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