How to View DICTIONARY Tables :: SAS(R) 9.3 Language Reference: Concepts, Second Edition

About Dictionary Tables

You might want to view the contents of DICTIONARY tables in order to see information about your current SAS session, before actually using the table in a DATA step or a SAS procedure.

Some DICTIONARY tables can become quite large. In this case, you might want to view a part of a DICTIONARY table that contains only the data that you are interested in. The best way to view part of a DICTIONARY table is to subset the table using a PROC SQL WHERE clause.

How to View a DICTIONARY Table

Each DICTIONARY table has an associated PROC SQL view in the SASHELP library. You can see the entire contents of a DICTIONARY table by opening its SASHELP view with the VIEWTABLE or FSVIEW utilities. This method provides more detail than you receive in the output of the DESCRIBE TABLE statement, as shown in How to View a Summary of a DICTIONARY Table.

The following steps describe how to use the VIEWTABLE or FSVIEW utilities to view a DICTIONARY table in a windowing environment.

Invoke the Explorer window in your SAS session.
Select the SASHELP library. A list of members in the SASHELP library appears.
Select a SAS view with a name that starts with V (for example, VMEMBER). A VIEWTABLE window appears that contains its contents. (For z/OS, type the letter 'O' in the command field for the desired member and press ENTER. The FSVIEW window appears with the contents of the view.)

In the VIEWTABLE window the column headings are labels. To see the column names, select View

Column Names.

How to View a Summary of a DICTIONARY Table

The DESCRIBE TABLE statement in PROC SQL produces a summary of the contents of a DICTIONARY table. The following example uses the DESCRIBE TABLE statement in order to generate a summary for the table DICTIONARY.INDEXES. (The SASHELP view for this table is SASHELP.VINDEX).

 proc sql; 
   describe table dictionary.indexes;

The result of the DESCRIBE TABLE statement appears in the SAS log:

NOTE: SQL table DICTIONARY.INDEXES was created like:

create table DICTIONARY.INDEXES
  (
   libname char(8) label='Library Name',
   memname char(32) label='Member Name',
   memtype char(8) label='Member Type',
   name char(32) label='Column Name', 
   idxusage char(9) label='Column Index Type', 
   indxname char(32) label='Index Name', 
   indxpos num label='Position of Column in Concatenated Key',
   nomiss char(3) label='Nomiss Option',
   unique char(3) label='Unique Option
  );

The first word on each line is the column (or variable) name, the name that you need to use when writing a SAS statement that refers to the column (or variable).
Following the column name is the specification for the type of variable and the width of the column.
The name that follows label= is the column (or variable) label.

After you know how a table is defined, you can use the processing ability of the PROC SQL WHERE clause in a PROC SQL step in order to extract a portion of a SAS view.

How to View a Subset of a DICTIONARY Table

When you know that you are accessing a large DICTIONARY and you need to use only a portion of it, use a PROC SQL WHERE clause in order to extract a subset of the original. The following PROC SQL statement demonstrates the use of a PROC SQL WHERE clause in order to extract lines from DICTIONARY.INDEXES.

options nodate;

proc sql;
   title 'Subset of the DICTIONARY.INDEX View';
   title2 'Rows with Column Name equal to STATE';
   select * from dictionary.indexes
      where name = 'STATE';
quit;

The results are shown in the following output:

Result of the PROC SQL Subsetting WHERE Statement

                         Subset of the DICTIONARY.INDEX View                  
                                            Rows with Column Name equal to STATE

                                                                                      Column
Library                                     Member                                    Index
Name      Member Name                       Type      Column Name                     Type      
                                     Position of
                                       Column in
                                    Concatenated  Nomiss  Unique
Index Name                                   Key  Option  Option
----------------------------------------------------------------------------------------------
MAPS      USAAC                             DATA      STATE                          COMPOSITE  
SC000000                                     0   no     no              

MAPS      USAAC                             DATA      STATE                          COMPOSITE  
CS000000                                     8   no     no                 

MAPS      USAAS                             DATA      STATE                          SIMPLE     
STATE                                        .   no     no

Note that many character values in the DICTIONARY tables are stored as all-uppercase characters; you should design your queries accordingly.

DICTIONARY Tables and Performance

When you query a DICTIONARY table, SAS gathers information that is pertinent to that table. Depending on the DICTIONARY table that is being queried, this process can include searching libraries, opening tables, and executing SAS views. Unlike other SAS procedures and the DATA step, PROC SQL can improve this process by optimizing the query before the select process is launched. Therefore, although it is possible to access DICTIONARY table information with SAS procedures or the DATA step by using the SASHELP views, it is often more efficient to use PROC SQL instead.

For example, the following programs both produce the same result, but the PROC SQL step runs much faster because the WHERE clause is processed before opening the tables that are referenced by the SASHELP.VCOLUMN view:

data mytable;
   set sashelp.vcolumn;
   where libname='WORK' and memname='SALES';
run;

proc sql;
   create table mytable as
      select * from sashelp.vcolumn
      where libname='WORK' and memname='SALES';
quit;

Note: SAS does not maintain DICTIONARY table information between queries. Each query of a DICTIONARY table launches a new discovery process.

If you are querying the same DICTIONARY table several times in a row, you can get even faster performance by creating a temporary SAS data set (with the DATA step SET statement or PROC SQL CREATE TABLE AS statement) with the information that you desire and run your query against that data set.