Metadata means ‘Data about data’. SAS metadata contains data about the current SAS session and everything in it. Not only about data sets, but every aspect of your session imaginable: system options, indexes, integrity constraints, SAS formats, and so on. This post introduces how to read SAS metadata in Dictionary Tables and SASHELP Views with PROC SQL and the Data Step.

Dictionary Tables in SAS

Dictionary tables are a set of read-only tables. They contain information about your current SAS session. The number of dictionary tables varies among versions of SAS. SAS 9.4 offers 32 dictionary tables. You can see a full list of them in the Dictionary Tables Documentation. The dictionary tables can only be referenced in a From Clause in PROC SQL. The data step can not read metadata directly through dictionary tables.

Let us look at a few examples of how to query metadata with PROC SQL in SAS. First, I simply use the Describe Table Statement to describe the dictionary.columns table. This is a nice way to get an overview. Next, I simply read the data. I am interested in the data set Sashelp.Class. I simply query the dictionary.columns table, use a Where Statement and select the desired values for libname and memname. These are columns in the dictionary.columns table. As you can see in the DictionaryClass data set contains rich information about the variables in the SASHELP.CLASS data set.

proc sql;
   describe table dictionary.columns;
quit;
 
proc sql;
   create table DictionaryClass as
   select *
   from dictionary.columns
   where libname='SASHELP'
     and memname='CLASS';
quit;

Let us look at another example. This time, we take a step back and consider entire data sets instead of the variables in them. The dictionary.tables table contains information about entire data sets. The query below creates a data set with information about all data sets in the Sashelp Library. Look in the DicTables data set. Here you can see all possible information about the data sets: Created data, Modified data, compression information and more.

proc sql;
   create table DicTables as
   select *
   from dictionary.tables
   where libname='SASHELP'
     and memtype='DATA';
quit;

Sashelp Views

You can not read dictionary tables with the data step. Luckily, there is another way to read metadata. Namely with the Sashelp Views. These views are created exactly from the original SQL metadata tables. We can read the Sashelp Views directly in a data step or from any other procedure for that matter. You can see all of the dictionary tables and their corresponding Sashelp Views in the documentation. The two data steps below create exactly the same data as the SQL queries above.

proc contents data=sashelp.vcolumn;
run;
 
data ViewColumns;
   set sashelp.vcolumn;
   where libname='SASHELP' and memname='CLASS';
run;
 
data ViewTables;
   set sashelp.vtable;
   where libname='SASHELP'
     and memtype='DATA';
run;

The ability to read SAS metadata directly from a data step has certain advantages. However, there are drawbacks to reading metadata without Proc SQL as well. When we query information with the SQL Procedure, an internal optimizer can speed up the process rapidly. This does not happen with the data step. Consequently, the speed can vary significantly between the two approaches. For more information, consult the documentation and Chapter 12 in PROC SQL: Beyond the Basics Using SAS.

Summary

This post introduces the concept of metadata and how to read it. I present two different approaches. The dictionary tables and Sashelp Views. Furthermore, I discuss the pros and cons of each technique. Naturally, you can do much more with SAS metadata than I do here. This post serves as a brief introduction. In the future, I will present more examples of how to exploit SAS metadata to your advantage.

I have used metadata techniques before in the blog post Drop or Keep Variables With The Same Suffix. Also, I use this technique along with Call Execute Logic in the post Dynamically Alter Data Set Attributes in SAS.

You can download the entire code from this post here.