52 Reading Data Chapter 7
statement to the data dictionary tables in your DBMS dynamically (by using
DBMS-specific call routines or interface software). The ACCESS procedure then issues
the equivalent of a DESCRIBE statement to gather information about the columns in
the specified table. The access descriptor’s information about the table and its columns
is then copied into the view descriptor when it is created. Therefore, it is not necessary
for SAS to call the DBMS when it creates a view descriptor.
The process is as follows:
1
When you supply the connection information to PROC ACCESS, the SAS/ACCESS
interface calls the DBMS to connect to the database.
2 SAS constructs a SELECT * FROM
table-name statement and passes it to the
DBMS to retrieve information about the table from the DBMS data dictionary. This
SELECT statement is based on the information you supplied to PROC ACCESS. It
enables SAS to determine whether the table exists and can be accessed.
3
The SAS/ACCESS interface calls the DBMS to get table description information,
such as the column names, data types (including width, precision, and scale), and
whether the columns accept null values.
4
SAS closes the connection with the DBMS.
Reading Data
When you use a view descriptor in a DATA step or procedure to read DBMS data, the
SAS/ACCESS interface view engine requests the DBMS to execute a SQL SELECT
statement. The interface view engine follows these steps:
1 Using the connection information that is contained in the created view descriptor,
the SAS/ACCESS interface calls the DBMS to connect to the database.
2 SAS constructs a SELECT statement that is based on the information stored in
the view descriptor (table name and selected columns and their characteristics)
and passes this information to the DBMS.
3 SAS retrieves the data from the DBMS table and passes it back to the SAS
procedures as if it were observations in a SAS data set.
4 SAS closes the connection with the DBMS.
For example, if you execute the following SAS program using a view descriptor, the
previous steps are executed once for the PRINT procedure and then a second time for
the GCHART procedure. (The data used for the two procedures is not necessarily the
same because the table might have been updated by another user between procedure
executions.)
proc print data=vlib.allemp;
run;
proc gchart data=vlib.allemp;
vbar jobcode;
run;
Updating Data
You use a view descriptor, DATA step, or procedure to update DBMS data in much
the same way as when reading data. Any of the following steps might also occur:
Using the connection information that is contained in the specified access
descriptor, the SAS/ACCESS interface calls the DBMS to connect to the database.
