How to View DICTIONARY Tables | SAS 9.1.3 Language Reference: Concepts, Third Edition, Volumes 1 and 2

You might want to view the contents of DICTIONARY tables in order to see information about your current SAS session, prior to 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" on page 562.

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 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

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 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.

 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:

Output 31.1: 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      Index Name  Position of    Column in Concatenated Nomiss Unique          Key Option Option ----------------------------------------------------------------------------------------------------------------------------------- MAPS     USAAC                 DATA     STATE                              COMPOSITE SC000000 0 MAPS     USAAC                 DATA     STATE                              COMPOSITE CS000000 8 MAPS     USAAS                 DATA     STATE                              SIMPLE   STATE           .

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 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 prior to 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.