The ACCESS Procedure for Relational Databases

Using Descriptors with the ACCESS Procedure

Descriptors work with the ACCESS procedure by providing information about DBMS objects to SAS, enabling you to access and update DBMS data from within a SAS session or program.

There are two types of descriptors, access descriptors and view descriptors. Access descriptors provide SAS with information about the structure and attributes of a DBMS table or view. An access descriptor, in turn, is used to create one or more view descriptors, or SAS data views, of the DBMS data.

Access Descriptors

Typically, each DBMS table or view has a single access descriptor that provides connection information, data type information, and names for databases, tables, and columns.

You use an access descriptor to create one or more view descriptors. When creating a view descriptor, you select the columns and specify criteria for the rows you want to retrieve. The figure below illustrates the descriptor creation process. Note that an access descriptor, which contains the metadata of the DBMS table, must be created before view descriptors can be created.

Creating an Access Descriptor and View Descriptors for a DBMS Table

[Creating an Access Descriptor and View Descriptors for a DBMS Table]

View Descriptors

You use a view descriptor in a SAS program much as you would any SAS data set. For example, you can specify a view descriptor in the DATA= statement of a SAS procedure or in the SET statement of a DATA step.

You can also use a view descriptor to copy DBMS data into a SAS data file, which is called extracting the data. When you need to use DBMS data in several procedures or DATA steps, you might use fewer resources by extracting the data into a SAS data file instead of repeatedly accessing the data directly.

The SAS/ACCESS interface view engine usually tries to pass WHERE conditions to the DBMS for processing. In most cases it is more efficient for a DBMS to process WHERE conditions than for SAS to do the processing.

Accessing Data Sets and Descriptors

SAS lets you control access to SAS data sets and access descriptors by associating one or more SAS passwords with them. When you create an access descriptor, the connection information that you provide is stored in the access descriptor and in any view descriptors based on that access descriptor. The password is stored in an encrypted form. When these descriptors are accessed, the connection information that was stored is also used to access the DBMS table or view. To ensure data security, you might want to change the protection on the descriptors to prevent others from seeing the connection information stored in the descriptors.

When you create or update view descriptors, you can use a SAS data set option after the ACCDESC= option to specify the access descriptor password, if one exists. In this case, you are not assigning a password to the view descriptor that is being created or updated. Instead, using the password grants you permission to use the access descriptor to create or update the view descriptor. Here is an example:

proc access dbms=sybase accdesc=adlib.customer
            (alter=rouge);
  create vlib.customer.view;
  select all;
run;

By specifying the ALTER level of password, you can read the AdLib.Customer access descriptor and create the Vlib.Customer view descriptor.

Top of Page