Previous Page | Next Page

How SAS/ACCESS Works

How the ACCESS Procedure Works


Overview of the ACCESS Procedure

When you use the ACCESS procedure to create an access descriptor, the SAS/ACCESS interface view engine requests the DBMS to execute an SQL SELECT 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. Access descriptor 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.

Here is the process:

  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 an 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 run the following SAS program using a view descriptor, the previous steps are executed once for the PRINT procedure and 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 a similar way as when you read in data. Any of these steps might also occur:

Previous Page | Next Page | Top of Page