How SAS/ACCESS Works

How the SAS/ACCESS LIBNAME Statement Works

You can use SAS/ACCESS to read, update, insert, and delete data from a DBMS object as if it were a SAS data set. Here is how to do that:

You start a SAS/ACCESS interface by specifying a DBMS engine name and the appropriate connection options in a LIBNAME statement.
You enter SAS requests as you would when accessing a SAS data set.
SAS/ACCESS generates DBMS-specific SQL statements that are equivalent to the SAS requests that you enter.
SAS/ACCESS submits the generated SQL to the DBMS.

The SAS/ACCESS engine defines which operations are supported on a table and calls code that translates database operations such as open, get, put, or delete into DBMS-specific SQL syntax. SAS/ACCESS engines use an established set of routines with calls that are tailored to each DBMS.

Processing Queries, Joins, and Data Functions

To enhance performance, SAS/ACCESS can also transparently pass queries, joins, and data functions to the DBMS for processing (instead of retrieving the data from the DBMS and then doing the processing in SAS). For example, an important use of this feature is the handling of PROC SQL queries that access DBMS data. Here is how it works:

PROC SQL examines each query to determine whether it might be profitable to send all or part of the query to the DBMS for processing.
A special query textualizer in PROC SQL translates queries (or query fragments) into DBMS-specific SQL syntax.
The query textualizer submits the translated query to the SAS/ACCESS engine for approval.
If SAS/ACCESS approves the translation, it sends an approval message to PROC SQL. The DBMS processes the query or query fragment and returns the results to SAS. Any queries or query fragments that cannot be passed to the DBMS are processed in SAS.

See performance considerations for detailed information about tasks that SAS/ACCESS can pass to the DBMS.

Top of Page