In this article you’ll learn hidden gems about DICTIONARY Tables In SAS. There are tons of useful use cases which many sas programmers aren’t aware of. This article exposes various aspects of DICTIONARY tables with detailed explanation.
What Is a DICTIONARY Table In SAS?
A DICTIONARY table is a read-only SAS view that contains information about SAS libraries, SAS data sets, SAS macros, and external files that are in use or available in the current SAS session. A DICTIONARY table also contains the settings for SAS system options that are currently in effect.
Dictionary tables in SAS are created and automatically maintained by the SAS system at run time to store information related to SAS data libraries, SAS system options, SAS catalogs, and external files associated with the currently running SAS session.
DICTIONARY tables can be accessed by a SAS program by using a PROC SQL query against the table, using the DICTIONARY libref.
For example: dictionary.dictionaries, dictionary.tables, dictionary.columns, dictionary.indexes, etc.
To know more about any specific dictionary tables, you can run a “describe table” query to print its source code.
/* describe dictionary tables */
proc sql;
describe table dictionary.tables ;
quit;
DICTIONARY Tables and SASHELP Views
As DICTIONARY tables can be accessed using the libref DICTIONARY in the FROM clause of a PROC SQL SELECT statement, SASHELP views can also be used to access the same information, the same dictionary tables.
For example, the SASHELP views such as sashelp.VTable, sashelp.VColumn, sashelp.VView, sashelp.VIndex, etc are pointing to its corresponding DICTIONARY tables.
You can describe any sashelp views using the PROC SQL statement to know what exactly the dictionary table is being used in the source code of that particular SASHELP view.
Here is how you can describe one of the SASHELP views.
/* describe SASHELP view sashelp.VTable */
proc sql;
describe view sashelp.vtable ;
quit;
DICTIONARY Tables and SASHELP Views
| DICTIONARY Table | SASHELP View | Purpose |
|---|---|---|
| CATALOGS | VCATALG | SAS Catalogs and Catalog-specific Information |
| CHECK_CONSTRAINTS | VCHKCON | Check Constraints information |
| COLUMNS | VCOLUMN | Columns from All Tables |
| CONSTRAINT_COLUMN_USAGE | VCNCOLU | Constraint Column Usage |
| CONSTRAINT_TABLE_USAGE | VCNTABU | Constraint Table Usage |
| DATAITEMS | VDATAIT | Information Map Data Items |
| DESTINATIONS | VDEST | Open ODS Destinations |
| DICTIONARIES | VDCTNRY | DICTIONARY Tables and their Columns |
| ENGINES | VENGINE | Available Engines |
| EXTFILES | VEXTFL | Implicitly-defined File Definitions and Files Defined in FILENAME statements |
| FILTERS | VFILTER | Information Map Filters |
| FORMATS | VFORMAT | Available SAS and User-defined Formats and Informats |
| FUNCTIONS | VFUNC | Available Functions |
| GOPTIONS | VGOPT | SAS/GRAPH Software Graphics Options |
| INDEXES | VINDEX | Information related to Defined Indexes |
| INFOMAPS | VINFOMP | Information Maps |
| LIBNAMES | VLIBNAM | Information related to SAS Data Libraries |
| LOCALES | VLOCALE | Available Locales, Regions, Languages, and Currency Symbols |
| MACROS | VMACRO | Information about Defined Macros |
| MEMBERS | VMEMBER | Information about SAS Defined Tables, Catalogs, and Views |
| OPTIONS | VOPTION | Information about SAS Default System Options |
| PROMPTS | VPROMPT | Information about Information Map Prompts |
| PROMPTSXML | VPRMXML | Information Map Prompts XML |
| REFERENTIAL_CONSTRAINTS | VREFCON | Information about Referential Constraints |
| REMEMBER | VREMEMB | All Remembered Information |
| STYLES | VSTYLE | Information about All Styles |
| TABLES | VTABLE | SAS Tables and Table-specific Information |
| TABLE_CONSTRAINTS | VTABCON | Information about Table Constraints |
| TITLES | VTITLE | Information about Defined Titles |
| VIEWS | VVIEW | Views and View-specific Information |
| VIEW_SOURCES | VSVIEW | Sources Referenced by View |
| XATTRS | VXATTR | Extended Attributes |
1. Extract List Of Dictionary Tables – [DICTIONARY.DICTIONARIES]
You already know that dictionary tables have all the system information including tables, views, columns, macros, and so on. But do you know what different dictionary tables are?
The easiest way to extract all the information about all the dictionary tables is by looking at the dictionary.dictionaries dataset.
You can perform simple “select * from” query on this dataset and you get the required information.
/* 1. Extract List Of Dictionary Tables */
proc sql;
create table dictionary_dictionaries as
select * from dictionary.dictionaries
;
quit;
If you look closely, it provides you lists of dictionary tables with their variable(column) names and its other granular details.
In case if you’re curious to know the unique list of available dictionary tables then you can execute the following query to list down just dictionary table names.
/* list unique dictionary tables */
proc sql;
select unique memname
from dictionary.dictionaries;
quit;
2. Extract Library Metadata Information – [DICTIONARY.LIBNAMES]
It’s always good practice to get hold of your entire environment by keeping track of all the necessary information such as tables, libraries, catalogs, and so on. SAS stores all the necessary details in the metadata and it can be retrieved using the relevant dictionary tables.
In order to extract metadata information about all the SAS libraries available in your environment, you can use dictionary.libnames
It holds library information such as library name, engine, physical path, system information, etc. Here is the entire overview of libraries that exist in my current SAS environment.
/* 2. Extract Library Metadata Information */
proc sql;
select * from dictionary.libnames;
quit;
In the above image, if you look closely, it provides you lists of libraries along with its other granular details.
In case if you’re curious to know a unique list of available libraries then you can execute the following query to list down unique lib names with its physical location.
/* list unique library and its physical path details */
proc sql;
select unique libname, path
from dictionary.libnames;
quit;
Remember: If you don’t know the actual column names of any dictionary tables then it is always good practice to run a “describe table” query. The physical column names of the dictionary.libnames table can be extracted by describing the table as follows.
/* describe dictionary table: libnames */
proc sql;
describe table dictionary.libnames ;
quit;3. Extract Table/Dataset Information – [DICTIONARY.TABLES]
As an SAS administrator or in general SAS developer who wants to know how many tables we have and what are those tables, making available dataset information is crucial.
With the help of dictionary tables you can gather all this information and extract meaningful information such as list of tables, member type, creation date, last modified date, no of observations, no of variables, size of a table, etc.
/* 3. Extract Table/Dataset Information */
proc sql;
create table dictionary_tables as
select * from dictionary.tables
;
quit;
Use case 1: Extract all the dataset names that exist in the WORK & MYLIB libraries.
/* list dataset names */
proc sql;
select unique libname, memname
from dictionary.tables where libname in ("WORK", "MYLIB");
quit;
Use case 2: How many total datasets and views do you have in the system?
/* dataset statistics */
proc sql;
select memtype, count(memname) as total_count from dictionary.tables
group by memtype
;
quit;
Use case 3: What is the index utilisation?
/* index utilization */
proc sql;
select indxtype,count(indxtype) as count from dictionary.tables
group by indxtype having count ne 0
;
quit;
Use case 4: What is the total size of your data?
/* total data size */
proc sql;
create table TotalDataSize as
select sum(filesize) as total_size from dictionary.tables
;
quit;
There would be many other use cases but I have highlighted here a few of them to give you some idea. It’s best to write a program to create a meaningful report based on extracted statistics and distribute it at least once a month.
4. Extract SAS Views Information – [DICTIONARY.VIEWS]
To generate overview of the SAS views defined in the system under different libraries, dictionary.views dictionary table can be used. It provides you all the details such as view name, library name, member type, engine name, etc.
/* 4. Extract SAS Views Information */
proc sql;
create table dictionary_views as
select * from dictionary.views
;
quit;
5. Extract Column Level Information [DICTIONARY.COLUMN]
In order to analyse SAS datasets further, dictionary.column gives you a detailed column (variable) level overview that includes, dataset name, column(variable name, type, length, label, format, informat, and so on. Let’s create a copy of an existing dictionary table to analyse further.
/* 5. Extract Column Level Information */
proc sql;
create table dictionary_column as
select * from dictionary.columns
;
quit;
In the above image it clearly says there are a total 25567 columns available in the current session. You can further analyse this dataset to learn more about the variables.
Use case: Extract list of columns with empty labels
It’s not necessary to have all the columns with labels. Your task is to find the columns with missing labels and list them down. It can be done using the missing(label) statement in the where clause.
/* columns with missing label */
proc sql;
create table missing_label as
select libname, memname, memtype, name, label from dictionary.columns
where missing(label)
;
quit;
6. Extract SAS Index Information [DICTIONARY.INDEXES]
We have already seen how to get the index utilisation stats using dictionary.tables dataset. With the dictionary.indexes you can extract more detailed information about the indexes that includes lib name, dataset, member type, column name, index type, index name, unique option, etc.
Let’s first create a copy of the dictionary.indexes table to analyse it further.
/* 6. Extract SAS dataset Index Information */
proc sql;
create table dictionary_indexes as
select * from dictionary.indexes
;
quit;
Use case: How many columns do you have with composite and simple indexes defined?
/* index statistics */
proc sql;
select idxusage, count(name) as total_count from dictionary.indexes
group by idxusage
;
quit;
7. Extract Macro Variable Values [DICTIONARY.MACROS]
In SAS advanced programming, it’s most common to create multiple macro variables and assign values to them. In one single shot if you want to see what macro variables available in the active session and their assigned values then DICTIONARY.MACROS is the table.
It provides you information such as Scope (Global, local, automatic), macro variable name, value, etc.
You can extract all the macro variable details from dictionary.macros.
/* 7. Extract Macro Variable Values */
proc sql;
select * from dictionary.macros
;
quit;
Just to verify, you can create a few macro variables and assign them any values you like. All those newly created macro variable’s details will be logged immediately in the dictionary.macros table by SAS software.
To demonstrate this we are creating four dummy macro variables with some random values. We are running the same query again to check if the newly created dummy macro variables are present.
/* create demo macro variables */
%let my_macro_var1= 'emo value for var 1';
%let my_macro_var2= 'demo value for var 2';
%let my_macro_var3= 100;
%let my_macro_var4= 200;
proc sql;
select * from dictionary.macros;
quit;
8. Extract Catalog Entries [DICTIONARY.CATALOGS]
SAS catalogs are special SAS files that store many different types of information in smaller units called catalog entries. Each entry has an entry type that identifies its purpose to SAS. A single SAS catalog can contain several types of catalog entries.
Some catalog entries contain system information such as key definitions. Other catalog entries contain application information such as window definitions, help windows, formats, informats, macros, or graphics output.
You can list the contents of a catalog using various SAS features, such as SAS Explorer and PROC CATALOG.
There would be 10,000+ catalogs that can be found as soon as you initiate a SAS session. To browse all of them you can use the DICTIONARY.CATALOGS table.
/* 8. Extract Catalog Entries */
proc sql;
create table dictionary_catalogs as
select * from dictionary.catalogs
;
quit;
Here you’ll find a lot of information to consume and process. If you have no idea about catalogs in SAS, the best way to start is to list down types of catalogs. You can use the same dataset which you have created to further analyse and understand SAS catalogs.
/* object type overview*/
proc sql;
select objtype, count(objtype) as ObjectTypes_Count
from dictionary_catalogs
group by objtype
;
quit;
Use Case: List macros stored in MACRO catalog
/* list macros stored in MACRO catalog*/
proc sql;
select libname,objname from dictionary.catalogs
where objtype = 'MACRO'
;
quit;Use Case: List formats stored in FORMAT catalog
/* list formats stored in FORMAT catalog*/
proc sql;
select libname,objname from dictionary.catalogs
where objtype = 'FORMAT'
;
quit;
9. Extract SAS Format Information [DICTIONARY.FORMATS]
A format is a type of SAS language element that applies a pattern to or executes instructions for a data value to be displayed or written as output.
Types of formats correspond to the type of data: numeric, character, date, time, or timestamp. The ability to create user-defined formats is also supported.
Examples of SAS formats are BINARY, DATE, and WORDS. For example, the WORDS22. format, which converts numeric values to their equivalent in words, writes the numeric value 692 as six hundred ninety-two.
The information about the SAS formats existing in the current SAS session can be extracted using the DICTIONARY.FORMATS table.
/* 9. Extract SAS Format Information */
proc sql;
select * from dictionary.formats;
quit;
10. Information About Currently Defined SAS Options
The simplest way to get an overview of currently defined SAS options is using the DICTIONARY.OPTIONS table.
It’s very convenient, especially when you want to set the different options. Tracking, maintaining, and getting exact information immediately can speed up your SAS options set up process.
/* 10. Information About Currently Defined SAS Options */
proc sql;
create table dictionary_options as
select * from dictionary.options
;
quit;
FAQ – DICTIONARY TABLES In SAS
Dictionary tables in SAS are special read-only tables that contain metadata about various aspects of the SAS environment. They provide valuable information about libraries, tables, columns, indexes, formats, options, and more. These tables are automatically created and maintained by SAS, allowing users to access and query metadata to better understand their SAS environment.
Dictionary tables can be accessed using SQL queries or PROC SQL statements in SAS. Users can retrieve metadata information from these tables by querying specific columns and filtering criteria.
Additionally, SAS provides PROC DATASETS and various procedures like PROC CONTENTS and PROC DATASETS to access metadata interactively or programmatically.
Dictionary tables contain a wide range of information about the SAS environment. Users can find details about libraries, tables, columns, indexes, integrity constraints, formats, options settings, and more.
This metadata is crucial for tasks such as data exploration, quality assessment, system monitoring, and performance tuning.
Dictionary tables themselves are read-only and cannot be directly modified by users. However, users can create views or subsets of Dictionary table data for specific analysis or reporting needs. Additionally, users can leverage the metadata stored in Dictionary tables to enhance their understanding of the SAS environment and make informed decisions about data management, system configuration, and optimization strategies.