2.4. Dictionary Tables

The SAS System generates and maintains valuable information at runtime about SAS libraries, data sets, catalogs, indexes, macros, system options, titles, and views in a collection of read-only tables called dictionary tables. Although called tables, dictionary tables are not real tables at all. Information is automatically generated at runtime and the tables’ contents are made available once a SAS session is started.

Dictionary tables and their contents permit a SAS session’s activities to be easily accessed and monitored. This becomes particularly important when building intelligent software applications. You can also specify other SELECT statement clauses, such as WHERE, GROUP BY, HAVING, and ORDER BY, when accessing dictionary tables. The available dictionary tables and their contents are described in the following table.

2.4.1. Displaying Dictionary Table Definitions

You can view a dictionary table’s definition and enhance your understanding of each table’s contents by specifying a DESCRIBE TABLE statement. The results of the statements used to create each dictionary table can be displayed on the SAS log. For example, a DESCRIBE TABLE statement is illustrated below to display the CREATE TABLE statement used in building the OPTIONS dictionary table containing current SAS System option settings.

SQL Code

PROC SQL;
  DESCRIBE TABLE
    DICTIONARY.OPTIONS;
QUIT;

SAS Log Results

create table DICTIONARY.OPTIONS
  (
   optname char(32) label='Option Name',
   setting char(1024) label='Option Setting',
   optdesc char(160) label='Option Description',
   level char(8) label='Option Location'
  );

2.4.2. Dictionary Table Column Names

To help you become familiar with each dictionary table’s and dictionary view’s column names and their definitions, the following table identifies each unique column name, type, length, format, informat, and label.

DICTIONARY.CATALOGS or SASHELP.VCATALG

DICTIONARY.COLUMNS or SASHELP.VCOLUMN

DICTIONARY.EXTFILES or SASHELP.VEXTFL

DICTIONARY.INDEXES or SASHELP.VINDEX

DICTIONARY.MACROS or SASHELP.VMACRO

DICTIONARY.MEMBERS or SASHELP.VMEMBER

DICTIONARY.OPTIONS or SASHELP.VOPTION

DICTIONARY.TABLES or SASHELP.VTABLE

DICTIONARY.TITLES or SASHELP.VTITLE

DICTIONARY.VIEWS or SASHELP.VVIEW

2.4.3. Accessing a Dictionary Table’s Contents

You access the content of a dictionary table with the SQL procedure’s SELECT statement FROM clause. Results are displayed as rows and columns in a table. The results can be used in handling common data processing tasks including obtaining a list of allocated libraries, catalogs, and data sets, as well as communicating SAS environment settings to custom software applications. You’ll want to take the time to explore the capabilities of these read-only dictionary tables and become familiar with the type of information they provide.

2.4.3.1. Dictionary.CATALOGS

Obtaining detailed information about catalogs and their members is quick and easy with the CATALOGS dictionary table. You can capture an ordered list of catalog information by member name including object name and type, description, date created and last modified, and object alias from any SAS library. For example, the following code produces a listing of the catalog objects in the SASUSER library.

SQL Code

PROC SQL;
  SELECT *
    FROM DICTIONARY.CATALOGS
      WHERE LIBNAME='SASUSER';
QUIT;

Results

2.4.3.2. Dictionary.COLUMNS

Retrieving information about the columns in one or more data sets is easy with the COLUMNS dictionary table. You can capture column-level information including column name, type, length, position, label, format, informat, and indexes, as well as produce cross-reference listings containing the location of columns in a SAS library. For example, the following code requests a cross-reference listing of the tables containing the CUSTNUM column in the WORK library.

SQL Code

PROC SQL;
  SELECT *
    FROM DICTIONARY.COLUMNS
      WHERE UPCASE(LIBNAME)='WORK' AND
            UPCASE(NAME)= 'CUSTNUM';
QUIT;

Results

2.4.3.3. Dictionary.EXTFILES

Accessing allocated external files by fileref and corresponding physical path name information is a snap with the EXTFILES dictionary table. The results from this handy table can be used in an application to communicate whether a specific fileref has been allocated with a FILENAME statement. For example, the following code produces a listing of each individual path name by fileref.

SQL Code

PROC SQL;
  SELECT *
    FROM DICTIONARY.EXTFILES;
QUIT;

Results

2.4.3.4. Dictionary.INDEXES

It is sometimes useful to display the names of existing simple and composite indexes, or their SAS tables, that reference a specific column name. The INDEXES dictionary table provides important information to help identify indexes that improve a query’s performance. For example, to display indexes that reference the CUSTNUM column name in any of the example tables, specify the following code.

SQL Code

PROC SQL;
  SELECT *
    FROM DICTIONARY.INDEXES
      WHERE UPCASE(NAME)='CUSTNUM'      /* Column Name  */
            AND UPCASE(LIBNAME)='WORK'; /* Library Name */
QUIT;

2.4.3.5. Dictionary.MACROS

The ability to capture macro variable names and their values is available with the MACROS dictionary table. The MACROS dictionary table provides information for global and macro variables, but not local macro variables. For example, to obtain columns specific to macros such as global macros SQLOBS, SQLOOPS, SQLXOBS, or SQLRC, specify the following code.

SQL Code

PROC SQL;
  SELECT *
    FROM DICTIONARY.MACROS
      WHERE UPCASE(SCOPE)='GLOBAL';
QUIT;

Results

2.4.3.6. Dictionary.MEMBERS

Accessing a detailed list of data sets, views, and catalogs is the hallmark of the MEMBERS dictionary table. You will be able to access a terrific resource of information by library, member name and type, engine, indexes, and physical path name. For example, to obtain a list of the individual files in the WORK library, the following code is specified.

SQL Code

PROC SQL;
  SELECT *
    FROM DICTIONARY.MEMBERS
      WHERE UPCASE(LIBNAME)='WORK';
QUIT;

Results

2.4.3.7. Dictionary.OPTIONS

The OPTIONS dictionary table provides a list of the current SAS System session’s option settings including the option name, its setting, description, and location. Obtaining option settings is as easy as 1-2-3. Simply submit the following SQL query referencing the OPTIONS dictionary table as follows. A partial listing of the results from the OPTIONS dictionary table is displayed below in rich text format.

SQL Code

PROC SQL;
  SELECT *
    FROM DICTIONARY.OPTIONS;
QUIT;

Results

2.4.3.8. Dictionary.TABLES

When you need more information about SAS files than what the MEMBERS dictionary table provides, consider using the TABLES dictionary table. The TABLES dictionary table provides such file details as library name, member name and type, date created and last modified, number of observations, observation length, number of variables, password protection, compression, encryption, number of pages, reuse space, buffer size, number of deleted observations, type of indexes, and requirements vector. For example, to obtain a detailed list of files in the WORK library, the following code is specified.

SQL Code

PROC SQL;
  SELECT *
    FROM DICTIONARY.TABLES
      WHERE UPCASE(LIBNAME)='WORK';
QUIT;

Results

2.4.3.9. Dictionary.TITLES

The TITLES dictionary table provides a listing of the currently defined titles and footnotes in a session. The table output distinguishes between titles and footnotes using a “T” or “F” in the TITLE LOCATION column. For example, the following code displays a single title and two footnotes.

SQL Code

PROC SQL;
  SELECT *
    FROM DICTIONARY.TITLES;
QUIT;

Results

2.4.3.10. Dictionary.VIEWS

The VIEWS dictionary table provides a listing of views for selected SAS libraries. The VIEWS dictionary table displays the library name, member names and type, and engine used. For example, the following code displays a single view called VIEW_CUSTOMERS from the WORK library.

SQL Code

PROC SQL;
  SELECT *
    FROM DICTIONARY.VIEWS
      WHERE UPCASE(LIBNAME)='WORK';
QUIT;

Results