Read SAS Metadata with Dictionary Tables and SASHELP Views
Metadata means ‘Data about data’. More specifically, SAS metadata contains data about the current SAS session. Not only about data sets, but every aspect of your session imaginable: system options, indexes, integrity constraints, 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 are a set of read-only tables. They contain information about your current SAS session. The number of dictionary tables vary 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 through dictionary tables.
Let us look at a few examples of quering metadata with PROC SQL. First, I simply use the Describe Table Statement to describe the dictionary.columns table. This is a nice way to get an overview of the table. Next, I simply read the data. Let’s say that I am interested in the data set SASHELPC.CLASS. I simply query the dictionary.columns table and use a Where Statement and select the desired values for libname and memname. These are variables in the dictionary.columns. 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 etc.
proc sql; create table DicTables as select * from dictionary.tables where libname='SASHELP' and memtype='DATA'; quit;
As I mentioned above, you can not read dictionary tables with the data step. Luckily, there is another way to read meta data. 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 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.
This post introduces the concept of metadata and how to read it. I present two different approaches, namely the dictionary tables and SASHELP Views. Furthermore, I discuss pros and cons between the techniques. Naturally, you can do much more with SAS metadata than I do here. This post serves as a brief Introduction to the concept. In the future, I will present more examples of how to exploit SAS metadata to your advantage.
I have used meta data 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.