How the ACCESS Procedure Works

Overview: ACCESS Procedure

When you use the ACCESS procedure to create an access descriptor, the SAS/ACCESS 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. It is therefore not necessary for SAS to call the DBMS when it creates a view descriptor. Here is the process.

1. When you provide 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 that you provided to PROC ACCESS. It lets SAS determine whether the table exists and can be accessed.
3. The SAS/ACCESS interface calls the DBMS to obtain 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. Here are the steps that the interface view engine follows.

1. Using the connection information that is 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 and passes this information to the DBMS. A view descriptor includes a table name and selected columns and their characteristics.
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 that is used for the two procedures is not necessarily the same because another user might have updated the table might have been updated between procedure executions.)

proc print data=vlib.allemp;
run;

proc gchart data=vlib.allemp;
   vbar jobcode;
run;

Updating Data

Use a view descriptor, DATA step, or procedure to update DBMS data similarly to when you read in data. Any of these steps might also occur.

1. Using the connection information that is contained in the specified access descriptor, the SAS/ACCESS interface calls the DBMS to connect to the database.
2. When rows are added to a table, SAS constructs an SQL INSERT statement and passes it to the DBMS. When you reference a view descriptor, use the ADD command in FSEDIT and FSVIEW, the APPEND procedure, or an INSERT statement in PROC SQL to add data to a DBMS table. (You can also use the EXECUTE statement for the SQL pass-through facility to add, delete, or modify DBMS data directly. You must use literal values when you insert data with the SQL pass-through facility.)
3. When rows are deleted from a DBMS table, SAS constructs an SQL DELETE statement and passes it to the DBMS. When you reference a view descriptor, you can use the DELETE command in FSEDIT and FSVIEW or a DELETE statement in PROC SQL to delete rows from a DBMS table.
4. When data in the rows is modified, SAS constructs an SQL UPDATE statement and passes it to the DBMS. When you reference a view descriptor, you can use FSEDIT, the MODIFY command in FSVIEW, or an INSERT statement in PROC SQL to update data in a DBMS table. You can also reference a view descriptor in the UPDATE, MODIFY, and REPLACE statements for the DATA step.
5. SAS closes the connection with the DBMS.