SAS/ACCESS Software for Relational Databases: Reference

ACCESS Procedure: Creating and Using Descriptor Files

SAS/ACCESS descriptor files are one way that SAS software uses to define data that is stored physically in a DBMS table or accessed through a DBMS view. To create descriptor files, you use the ACCESS procedure. There are two kinds of descriptor files: access descriptors and view descriptors.

Access Descriptors

An access descriptor holds essential information about the structure and attributes of the DBMS table or view you want to access, such as DBMS connection information, the name of the database, the table name and column names, and data types. It can also contain the corresponding SAS software information such as the SAS variable names and formats. Typically, you have only one access descriptor for each table.

An access descriptor only describes a DBMS table's format and contents to SAS software; that is, it is a master file of the table's information for SAS software. You cannot use an access descriptor in a SAS program; rather, you use an access descriptor to create other SAS files, called view descriptors, that you use in SAS programs.

Creating an Access Descriptor and View Descriptors for a DBMS Table illustrates the access 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

[IMAGE]

View Descriptors

A view descriptor defines some or all of the data that is described by one access descriptor. In other words, it defines all or part of one DBMS table or view. For example, you might want to use only three of nine possible columns and only some of the rows in a DBMS table. The view descriptor enables you to do this by selecting the columns that you want to use and by specifying criteria for retrieving, grouping, or ordering only the rows that you want. Typically, you create several view descriptors based on one access descriptor; each view descriptor selects a different subset of the DBMS data.

A view descriptor is a SAS data set or, more specifically, a SAS data view. 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.

A view descriptor can also be used to update data in a DBMS table directly, if you have been granted the authority to do so. For example, you can specify a view descriptor to add or delete rows in a DBMS table or to change the values in a field by using the SQL procedure or the View Table option in the SAS Explorer window. See Using DBMS Data with the SQL Pass-Through Facility for examples of updating DBMS data.

In some cases, you might also want to create a SAS data file from DBMS data. When you use a view descriptor to copy DBMS data into a SAS data file, it is called extracting the data. You can extract DBMS data in a number of ways. For example, you can extract the data by using various methods within the ACCESS procedure, a DATA step, or the OUT= option in a SAS procedure. (See ACCESS Procedure Syntax for more information.) When you need to use DBMS data in a number of procedures or DATA steps, extracting the data into a SAS data file may use fewer resources than repeatedly accessing the data directly.

Creating an Access Descriptor and View Descriptors for a DBMS Table illustrates the relationships among a DBMS table, an access descriptor, and one or more view descriptors. Using a View Descriptor to Access a DBMS Table illustrates the relationship between view descriptors, which store a query, and a DBMS tables or view.

Using a View Descriptor to Access a DBMS Table

[IMAGE]

Chapter Contents
Previous
Next
Top of Page