Dictionary tables are commonly used to monitor and manage SAS sessions because the data is easier to manipulate than the output from procedures such as PROC DATASETS.

Dictionary tables are special, read-only SAS tables that contain information about SAS libraries, SAS macros, and external files that are in use or available in the current SAS session. Dictionary tables also contain the settings for SAS system options and SAS titles and footnotes that are currently in effect. For example, the Dictionary.Columns table contains information (such as name, type, length, and format) about all columns in all tables that are known to the current SAS session.

Dictionary tables are

Accessing a Dictionary table causes SAS to determine the current state of the SAS session and return the information that you want. Dictionary tables can be accessed by running a PROC SQL query against the table, using the Dictionary libref. Though SAS librefs are usually limited to eight characters, Dictionary is an automatically assigned, reserved word. You can also access a Dictionary table by referring to the PROC SQL view of the table that is stored in the Sashelp library.

The following table describes some of the Dictionary tables that are available and lists the corresponding Sashelp views. For a complete list of Dictionary tables, see the SAS documentation for the SQL procedure.

You can query Dictionary tables the same way you query any other table, including subsetting with a WHERE clause, ordering the results, creating tables, and creating PROC SQL views. Because Dictionary tables are read-only objects, you cannot insert rows or columns, alter column attributes, or add integrity constraints to them.

To see how each Dictionary table is defined, submit a DESCRIBE TABLE statement. The DESCRIBE TABLE statement writes a CREATE TABLE statement to the SAS log for the table specified in the DESCRIBE TABLE statement. After you know how a table is defined, you can use its column names in a subsetting WHERE clause in order to retrieve specific information.

The Dictionary.Tables table contains detailed information about tables. The following DESCRIBE TABLE statement displays information about the Dictionary.Tables table in the log window. The information includes the names of the columns stored in the table.

proc sql;
   describe table dictionary.tables;

create table DICTIONARY.TABLES  
  (  
   libname char(8) label='Library Name', 
   memname char(32) label='Member Name', 
   memtype char(8) label='Member Type', 
   memlabel char(256) label='Dataset Label',  
   typemem char(8) label='Dataset Type', 
   crdate num format=DATETIME informat=DATETIME label='Date Created', 
...);

To display information about the files in a specific library, specify the column names in a SELECT statement and the Dictionary table name in the FROM clause.

For example, the following PROC SQL step displays the columns

The Dictionary column names are specified in the SELECT statement and the Dictionary table name, Dictionary.Tables, is specified in the FROM clause. The library name, Sasuser, is specified in the WHERE clause.

proc sql;
   select memname format=$20., nobs, nvar, crdate
      from dictionary.tables
      where libname='SASUSER';

Partial output is shown below.

You can also use Dictionary tables to determine more specific information such as which tables in a SAS library contain a specific column.

The Dictionary.Columns table contains detailed information about variables and their attributes. As in Dictionary.Tables, the Dictionary.Columns table contains a column that is titled Memname, which lists the name of each table within a library.

proc sql;
   describe table dictionary.columns;

create table DICTIONARY.COLUMNS  
  (   
   libname char(8) label='Library Name',  
   memname char(32) label='Member Name', 
   memtype char(8) label='Member Type', 
   name char(32) label='Column Name', 
   type char(4) label='Column Type', 
   length num label='Column Length',
...);

The following PROC SQL step lists all the tables in the Sasuser library that contain a column named EmpID. The dictionary column name, Memname, is specified in the SELECT statement. The Dictionary table, Dictionary.Columns, is specified in the FROM clause. The library name, Sasuser, and the column name, EmpID, are specified in the WHERE clause.

proc sql;
   select memname
      from dictionary.columns
      where libname='SASUSER'
            and name='EmpID';

Partial output is shown below.

Remember that you can also access a Dictionary table by referring to the PROC SQL view of the table that is stored in the Sashelp library. In the following PROC PRINT step, the Sashelp view Vcolumn is specified in the DATA= option. The results of the PROC PRINT step are identical to the preceding output.

proc print data=sashelp.vcolumn;
   var memname;
   where libname='SASUSER' and name='EmpID';
run;