SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition

Accessing SAS System Information By Using

DICTIONARY Tables

What Are DICTIONARY Tables?

DICTIONARY tables are special read-only PROC SQL tables or views. They retrieve

information about all the SAS libraries, SAS data sets, SAS system options, and external

files that are associated with the current SAS session. 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.

PROC SQL automatically assigns the DICTIONARY libref. To get information from

DICTIONARY tables, specify DICTIONARY.table-name in the FROM clause in a

SELECT statement in PROC SQL.

DICTIONARY.table-name is valid in PROC SQL only. However, SAS provides PROC

SQL views, based on the DICTIONARY tables, that can be used in other SAS

procedures and in the DATA step. These views are stored in the Sashelp library and are

commonly called “Sashelp views.”

For an example of a DICTIONARY table, see “Example 6: Reporting from

DICTIONARY Tables” on page 281.

The following table describes the DICTIONARY tables that are available and shows the

associated Sashelp views for each table.

Table 5.1 DICTIONARY Tables and Associated Sashelp Views

DICTIONARY Table

Sashelp

View Description

CATALOGS Vcatalg Contains information about known SAS catalogs.

CHECK_CONSTRAINTS Vchkcon Contains information about known check constraints.

COLUMNS Vcolumn Contains information about columns in all known tables.

CONSTRAINT_COLUMN_USAGE Vcncolu Contains information about columns that are referred to

by integrity constraints.

CONSTRAINT_TABLE_USAGE Vcntabu Contains information about tables that have integrity

constraints defined on them.

DATAITEMS Vdatait Contains information about known information map data

items.

DESTINATIONS Vdest Contains information about known ODS destinations.

DICTIONARIES Vdctnry Contains information about all DICTIONARY tables.

ENGINES Vengine Contains information about SAS engines.

Accessing SAS System Information By Using DICTIONARY Tables 149

DICTIONARY Table

Sashelp

View Description

EXTFILES Vextfl Contains information about known external files.

FILTERS Vfilter Contains information about known information map

filters.

FORMATS Vformat

Vcformat

Contains information about currently accessible formats

and informats.

FUNCTIONS Vfunc Contains information about currently accessible

functions.

GOPTIONS Vgopt

Vallopt

Contains information about currently defined graphics

options (SAS/GRAPH software). Sashelp.Vallopt

includes SAS system options as well as graphics options.

INDEXES Vindex Contains information about known indexes.

INFOMAPS Vinfomp Contains information about known information maps.

LIBNAMES Vlibnam Contains information about currently defined SAS

libraries.

MACROS Vmacro Contains information about currently defined macro

variables.

MEMBERS Vmember

Vsacces

Vscatlg

Vslib

Vstable

Vstabvw

Vsview

Contains information about all objects that are in

currently defined SAS libraries. Sashelp.Vmember

contains information for all member types; the other

Sashelp views are specific to particular member types

(such as tables or views).

OPTIONS Voption

Vallopt

Contains information about SAS system options.

Sashelp.Vallopt includes graphics options as well as

SAS system options.

REFERENTIAL_CONSTRAINTS Vrefcon Contains information about referential constraints.

REMEMBER Vrememb Contains information about known remembers.

STYLES Vstyle Contains information about known ODS styles.

TABLE_CONSTRAINTS Vtabcon Contains information about integrity constraints in all

known tables.

TABLES Vtable Contains information about known tables.

150 Chapter 5 • Programming with the SQL Procedure

DICTIONARY Table

Sashelp

View Description

TITLES Vtitle Contains information about currently defined titles and

footnotes.

VIEWS Vview Contains information about known data views.

VIEW_SOURCES Not available Contains a list of tables (or other views) referenced by

the SQL or DATASTEP view, and a count of the number

of references.

XATTRS Vxattr Contains information about extended attributes.

Retrieving Information about DICTIONARY Tables and Sashelp

Views

To see how each DICTIONARY table is defined, submit a DESCRIBE TABLE

statement. This example shows the definition of DICTIONARY.Tables:

proc sql;

describe table dictionary.tables;

The results are written to the SAS log.

Accessing SAS System Information By Using DICTIONARY Tables 151