Previous Page | Next Page

Programming with the SQL Procedure

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 Reporting from DICTIONARY Tables in the Base SAS Procedures Guide.

The following table describes the DICTIONARY tables that are available and shows the associated SASHELP views for each table.

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.
EXTFILES VEXTFL Contains information about known external files.
FILTERS VFILTER Contains information about known information map filters.


Contains information about currently accessible formats and informats.
FUNCTIONS VFUNC Contains information about currently accessible functions.


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.







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


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.
TITLES VTITLE Contains information about currently defined titles and footnotes.
VIEWS VVIEW Contains information about known data views.

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.


NOTE: SQL table DICTIONARY.TABLES was created like:

   libname char(8) label='Library Name',
   memname char(32) label='Member Name',
   memtype char(8) label='Member Type',
   dbms_memtype char(32) label='DBMS Member Type',
   memlabel char(256) label='Data Set Label',
   typemem char(8) label='Data Set Type',
   crdate num format=DATETIME informat=DATETIME label='Date Created',
   modate num format=DATETIME informat=DATETIME label='Date Modified',
   nobs num label='Number of Physical Observations',
   obslen num label='Observation Length',
   nvar num label='Number of Variables',
   protect char(3) label='Type of Password Protection',
   compress char(8) label='Compression Routine',
   encrypt char(8) label='Encryption',
   npage num label='Number of Pages',
   filesize num label='Size of File',
   pcompress num label='Percent Compression',
   reuse char(3) label='Reuse Space',
   bufsize num label='Bufsize',
   delobs num label='Number of Deleted Observations',
   nlobs num label='Number of Logical Observations',
   maxvar num label='Longest variable name',
   maxlabel num label='Longest label',
   maxgen num label='Maximum number of generations',
   gen num label='Generation number',
   attr char(3) label='Data Set Attributes',
   indxtype char(9) label='Type of Indexes',
   datarep char(32) label='Data Representation',
   sortname char(8) label='Name of Collating Sequence',
   sorttype char(4) label='Sorting Type',
   sortchar char(8) label='Charset Sorted By',
   reqvector char(24) format=$HEX48 informat=$HEX48 label='Requirements Vector',
   datarepname char(170) label='Data Representation Name',
   encoding char(256) label='Data Encoding',
   audit char(8) label='Audit Trail Active?',
   audit_before char(8) label='Audit Before Image?',
   audit_admin char(8) label='Audit Admin Image?',
   audit_error char(8) label='Audit Error Image?',
   audit_data char(8) label='Audit Data Image?',
   num_character num label='Number of Character Variables',
   num_numeric num label='Number of Numeric Variables'

Similarly, you can use the DESCRIBE VIEW statement in PROC SQL to determine how a SASHELP view is defined. Here is an example:

proc sql;
   describe view sashelp.vtable;

Description of SASHELP.VTABLE

NOTE: SQL view SASHELP.VSTABVW is defined as:

        select libname, memname, memtype
         where (memtype='VIEW') or (memtype='DATA')
      order by libname asc, memname asc;


DICTIONARY tables are commonly used to monitor and manage SAS sessions because the data is more easily manipulated than the output from other sources such as PROC DATASETS. You can query DICTIONARY tables the same way that you query any other table, including subsetting with a WHERE clause, ordering the results, and creating PROC SQL views.

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

Because DICTIONARY tables are read-only objects, you cannot insert rows or columns, alter column attributes, or add integrity constraints to them.

Note:   For DICTIONARY.TABLES and SASHELP.VTABLE, if a table is read-protected with a password, then the only information that is listed for that table is the library name, member name, member type, and type of password protection. All other information is set to missing.  [cautionend]

Note:   An error occurs if DICTIONARY.TABLES is used to retrieve information about an SQL view that exists in one library but has an input table from a second library that has not been assigned.  [cautionend]

The following query uses a SELECT and subsetting WHERE clause to retrieve information about permanent tables and views that appear in the SQL library:

proc sql;
   title 'All Tables and Views in the SQL Library';
   select libname, memname, memtype, nobs
      from dictionary.tables
      where libname='SQL';

Tables and Views Used in This Document

                    All Tables and Views in the SQL Library

                                                                Number of
       Library                                     Member        Physical
       Name      Member Name                       Type      Observations
       SQL       A                                 DATA                 4
       SQL       B                                 DATA                 3
       SQL       CITYREPORT                        DATA               132
       SQL       CONTINENTS                        DATA                 9
       SQL       COUNTRIES                         DATA               209
       SQL       DENSITIES                         DATA                10
       SQL       EXTREMETEMPS                      DATA                20
       SQL       FEATURES                          DATA                76
       SQL       MYSTATES                          DATA                 0
       SQL       NEWCONTINENTS                     VIEW                 .
       SQL       NEWCOUNTRIES                      DATA                 6
       SQL       NEWPOP                            DATA                14
       SQL       NEWSTATES                         DATA                 0
       SQL       OILPROD                           DATA                31
       SQL       OILRSRVS                          DATA                26
       SQL       POSTALCODES                       DATA                59
       SQL       REFEREE                           DATA                 4
       SQL       STATECODES                        DATA                51
       SQL       UNITEDSTATES                      DATA                57
       SQL       USCITYCOORDS                      DATA               132
       SQL       USPOSTAL                          DATA                 0
       SQL       WORLDCITYCOORDS                   DATA               222
       SQL       WORLDCOUNTRIES                    DATA               208
       SQL       WORLDTEMPS                        DATA                59


DICTIONARY tables are useful when you want to find specific columns to include in reports. The following query shows which of the tables that are used in this document contain the Country column:

proc sql;
   title 'All Tables That Contain the Country Column';
   select libname, memname, name
      from dictionary.columns
      where name='Country' and

Using DICTONARY.COLUMNS to Locate Specific Columns

                   All Tables That Contain the Country Column

  Name      Member Name                       Column Name
  SQL       OILPROD                           Country                         
  SQL       OILRSRVS                          Country                         
  SQL       WORLDCITYCOORDS                   Country                         
  SQL       WORLDTEMPS                        Country                         

DICTIONARY Tables and Performance

When querying a DICTIONARY table, SAS launches a discovery process that gathers information that is pertinent to that table. Depending on the DICTIONARY table that is being queried, this discovery process can search libraries, open tables, and execute views. Unlike other SAS procedures and the DATA step, PROC SQL can mitigate this process by optimizing the query before the discovery 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.

Note:   You cannot use data set options with DICTIONARY tables.  [cautionend]

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

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

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

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

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

When you query DICTIONARY.TABLES or SASHELP.VTABLE, all the tables and views in all the libraries that are assigned to the SAS session are opened to retrieve the requested information.

You can use a WHERE clause to help restrict which libraries are searched. However, the WHERE clause will not process most function calls such as UPCASE.

For example, if where UPCASE (libname) ='WORK' is used, the UPCASE function prevents the WHERE clause from optimizing this condition. All libraries that are assigned to the SAS session are searched. Searching all the libraries could cause an unexpected increase in search time, depending on the number of libraries that are assigned to the SAS session.

The DICTIONARY information for LIBNAME and MEMNAME values is stored in uppercase. If you supply values for LIBNAME and MEMNAME values in uppercase; that is, if you remove the UPCASE function, the WHERE clause will be optimized and performance will be improved. In the previous example, the code would be changed to where libname='WORK' .

Note:   If you query table information from a library that is assigned to an external database and you use the LIBNAME statement PRESERVE_TAB_NAMES=YES option, you should supply the LIBNAME and MEMNAME values in uppercase and place the MEMNAME keyword in the UPCASE function.

If you query column information from a library that is assigned to an external database and you use the LIBNAME statement PRESERVE_COL_NAMES=YES option, you should supply the NAME value in uppercase and place the NAME keyword in the UPCASE function.  [cautionend]

Previous Page | Next Page | Top of Page