|Programming with the SQL Procedure|
|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 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:
The results are written to the SAS log.
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:
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;
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.
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.
proc sql; title 'All Tables and Views in the SQL Library'; select libname, memname, memtype, nobs from dictionary.tables where libname='SQL';
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 libname='SQL';
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.
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:
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, 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.