Programming with the SQL Procedure

Accessing SAS System Information by Using 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.
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.
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.

Definition of DICTIONARY.TABLES

NOTE: SQL table DICTIONARY.TABLES was created like:

create table DICTIONARY.TABLES
  (
   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
          from DICTIONARY.MEMBERS
         where (memtype='VIEW') or (memtype='DATA')
      order by libname asc, memname asc;

Using DICTIONARY.TABLES

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

Using DICTIONARY.COLUMNS

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
            libname='SQL';

Using DICTONARY.COLUMNS to Locate Specific Columns

                   All Tables That Contain the Country Column

  Library
  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';
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. [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]

Top of Page