Using PROC SQL to access DICTIONARY Tables

Dictionary tables contain vital information about your current SAS session.  These special tables can only be used with PROC SQL whereas the views can be used with any SAS Procedures and Data Steps.

These special DICTIONARY Tables are automatically created each time they are referenced in a SAS program and store the following information about the current SAS session.

·         Libraries

·         SAS Macros

·         External Files

·         Tables

·         SAS System Options Settings

·         SAS Titles

·         SAS Footnotes

·         Columns

·         Views

·         Data Sets

One of the best kept secrets in SAS are DICTIONARY Tables. Did you know that when running SAS, there are special read-only PROC SQL Tables or views called DICTIONARY Tables that contain large 

SAS DICTIONARY Tables are mostly used to monitor and manage SAS sessions because the data they contain can be easily manipulated, particularly with PROC SQL. 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. The same information can be found in the SASHELP.Vcolumn view.



PROC SQL can be used to query DICTIONARY Tables in the same way they are used with any other SAS table, dataset or view. PROC SQL can be used with DICTIONARY Tables to perform:

·         Horizontal joins

·         Vertical joins

·         Correlated Subqueries

·         Noncorrelated Subqueries

·         WHERE clause subsetting

·         Creation of other tables or views

Of importance to note is that SAS DICTIONARY Tables are read-only, which means that they cannot be altered by the programmer. This means that columns and rows can never be inserted, deleted, altered or modified in a SAS DICTIONARY Table. All changes in a SAS DICTIONARY Table are done automatically by the SAS system as more and more information is collected about the current SAS session.


Remember that SAS DICTIONARY Tables can be accessed using the Dictionary libref e.g DICTIONARY.TABLES. The online SAS documentation states that because DICTIONARY.table-name is valid only in PROC SQL, SAS provides PROC SQL views that are based on the DICTIONARY Tables which can be used in other SAS Procedures and Data Steps. Remember, that these PROC SQL views are stored in the SAS HELP library e.g SASHELP.Vtable.


Normally the first logical step when working with a SAS DICTIONARY Table is to run the PROC SQL DESCRIBE TABLE statement in order to obtain full information about that DICTIONARY Table. This will aid significantly when proceeding with the PROC SQL query because a DESCRIBE TABLE statement always outputs a PROC SQL CREATE TABLE statement to the SAS log. This CREATE TABLE statement will contain detailed information about all the columns of the particular DICTIONARY.table-name the programmer is working with.


See the following link for additional in depth information about SAS DICTIONARY Tables and their associated SASHELP Views. http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001385596.htm

Happy Learning!