SAS Dictionary Tables : Navigation compass in SAS programmers backpack

No matter where you stand on the Planet Earth, you can hold a compass in your hand and it will point toward the North Pole.. What an unbelievably neat, amazing and a must have thing in any travelling Backpack ! You can ask Bear Grylls about its importance in travelling Bag.

Imagine that you in the middle of the SAS programming Session and you need something (just like compass) which can helps you orient yourself about the SAS session,and get familiar with datasets and their contents. We don’t have compasses in SAS but we have something equally efficient that can be quite handy and useful in helping us find the best direction to move forward?

These compass equivalent are Dictionary tables which can be very powerful tools if used efficiently  to obtain information in your SAS Session .

What are Dictionary Tables?

Dictionary tables and views contain information about each SAS session or batch job. These special tables and views are available immediately after the SAS session starts, and are updated automatically by SAS throughout the entire session.

Dictionary tables are Read-Only and contain data or metadata about the SAS session. Because dictionary tables are Read-Only, you cannot insert rows or columns, alter column attributes, or add integrity constraints to them.

Dictionary tables contain information about SAS libraries, SAS data sets and tables, other SAS files available in SAS libraries, SAS system option names  and the settings that are currently in effect, SAS macro variable names and values, external files that are available for the current SAS session, and title and footnote text. SAS automatically assigns the special reserved libref dictionary, accessible only from within PROC SQL, to the dictionary tables.

For example, the table dictionary.dictionaries contains information about the contents of the dictionary tables. Within the dictionary tables, SAS stores library and table names in uppercase. But, SAS stores column names in the dictionary tables in the same case in which they were defined when created.

So, the column names can be all lowercase, all uppercase, or mixed case.

There can be more than 30 dictionary tables. The most used tables are described shortly below.

Dictionary.tables contains detailed information about all tables in a SAS session, dictionary.columns contains detailed information about all columns in all tables, and dictionary.members contains detailed information about SAS library members.

Querying Dictionary Information

When you query a dictionary table, SAS gathers information that is pertinent to the specified table and creates the dictionary table.

Depending on the table that is being queried, this process can include searching libraries, opening tables, and executing SAS views. Some of these dictionary tables can get quite large.

The dictionary tables are accessible only with PROC SQL.

SAS provides PROC SQL views, based on the dictionary tables, which can be used in other SAS programming steps such as the DATA or PROC step. These views are stored in the sashelp library and are commonly called SASHELP views.

Unlike the DATA or PROC step, PROC SQL can improve the information gathering process by optimizing the query before the information is gathered. So, while you  can access dictionary information with SAS procedures or the DATA step, it is often more efficient to use PROC SQL instead.

To prepare for writing a specific query, you can use a DESCRIBE statement to explore the structure of dictionary tables. When you’re working with tables, you specify the keywords DESCRIBE TABLE, and then one or more table names, separated by a comma.

The DESCRIBE TABLE statement writes the attributes of the columns in dictionary.tables to the SAS log. Because the libref dictionary is automatically assigned, you don’t need to use a LIBNAME statement to run this code.

Displaying Dictionary Table Definitions

A dictionary table’s definition can be displayed by specifying a DESCRIBE TABLE statement. The results of the statements and clauses used to create each dictionary table can be displayed on the SAS Log. For example, a DESCRIBE TABLE statement is illustrated below to display the CREATE TABLE statement used in building the OPTIONS dictionary table containing current SAS System option settings.

PROC SQL Code

PROC SQL;

DESCRIBE TABLE DICTIONARY.OPTIONS;

QUIT;

Using Dictionary Tables in Other SAS Code

In SAS procedures and the DATA step, you must refer to sashelp instead of dictionary.

PROC SQL views based on the dictionary tables are stored in the sashelp library. In addition, in PROC and DATA steps, the libref cannot exceed eight characters.

Most of the sashelp library dictionary view names are similar to dictionary table names, but they are shortened to eight characters or fewer. They begin with the letter v, and do not end in s.

You can refer SAS Help and Documentation for a complete list of the views available.

Dictionary Tables and Purposes:

sas table

sas table 2

sas table 3