O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required