Dictionary Tables: A SAS Hidden Gem

1 Like

What are dictionary tables and how can we use them? SAS dictionary tables are read-only tables that contain information about your current SAS session. They're updated automatically by SAS and can be accessed using PROC SQL. In this post, we'll explore how these tables can be used to examine datasets in a library, identify common columns, create a macro variable list of column names, and troubleshoot macro programs.

Examples throughout this post were created in SAS Viya for Learners and focus on data for the fictitious company Chocolate Enterprise. The tables include information about customers, their orders and the items they purchase.

I created a library named CHOC for the data in Explorer> Files> Home> Courses> Academic and copied the data from the Academic folder into my own folder named ChocolateEnterprise using the following code:

libname in '/export/viya/homes/carleighjo.crabtree@sas.com/Courses/ACADEMIC';

libname choc '/export/viya/homes/carleighjo.crabtree@sas.com/casuser/ChocolateEnterprise';

proc copy inlib=in outlib=choc noclone datecopy ;
run;

Explore Datasets in a Library

When exploring unfamiliar libraries or documenting your environment, DICTIONARY.tables is very helpful. It contains detailed information about the tables in your SAS session.

The DESCRIBE TABLE statement prints the columns and labels in DICTIONARY.tables to the log. This information is useful as the column labels are printed in reports by default when working with dictionary tables.

proc sql;
describe table dictionary.tables;
quit;

Partial results:

Notice that the libname column contains library names, and memname contains table names.

Question:

What tables contain information about Chocolate Enterprise in the CHOC library?

Use the WHERE clause to filter for tables in the CHOC library.

proc sql;
select *
     from dictionary.tables
     where libname="CHOC";
quit;

Partial results:

The tables containing data about Chocolate Enterprise all begin with the prefix "choc":

chocolate_enterprise_reporting
choc_enterprise_customer
choc_enterprise_item
choc_enterprise_orders

To view only these tables, add another filter. Note that the values in libname and memname must be in all caps.

proc sql;
select *
     from dictionary.tables
     where libname="CHOC" and memname like "CHOC%" ;
quit;

Partial results:

You can now easily see the number of observations, variables and other metadata for each table.

Finding Common Columns

When joining tables, flipping back and forth to find common columns can be a pain. DICTIONARY.columns contains detailed information about all columns in all tables- including name, type, and length- which makes it ideal for identifying join keys.

View the column names and attributes in the log with DESCRIBE TABLE.

Partial results:

Question:

What are potential common columns between choc_enterprise_customer, choc_enterprise_item and choc_enterprise_orders?

In a new report, filter for the CHOC library and tables that start with "CHOC_".

proc sql;
select *
     from dictionary.columns
     where libname="CHOC" and memname like "CHOC/_%" escape '/';
quit;

Partial results:

You may notice repeated column names or patterns. For example, when the column name has "rk" in it, the label is a key. We can use this to further filter our search.

proc sql;
select *
     from dictionary.columns
     where libname="CHOC" and memname like "CHOC/_%" escape '/' and name like "%rk";
quit;

Partial results:

This report reveals customer_rk is shared between the customers and orders tables, and item_rk is shared between the orders and item tables. This report also verifies customer_rk and item_rk are the same data type and length in their respective tables before joining. The inner join could be performed as follows:

proc sql;
create table customer_orders_items as
select *
     from choc.choc_enterprise_customer as c
     inner join choc.choc_enterprise_orders as o
          on c.customer_rk=o.customer_rk
     inner join choc.choc_enterprise_item as i
          on i.item_rk=o.item_rk;
quit;

Creating a Macro Variable List of Column Names

Another way to use DICTIONARY.columns is to put column names from a table into a macro variable list.

The following table spl_transposed was created to use with summary functions.

Jump to code to create spl_transposed.

When working with summary functions across columns, the use of column lists is not permitted, meaning column names must be listed out. The following program summarizes the max, min, and average number of orders by product line for each state.

proc sql;
select state_region_nm label= "State Name",
       max(Assorted,Misc_Pack,Candy,Drinks,Books,Snacks) as HighestOrders format= comma16.,
       min(Assorted,Misc_Pack,Candy,Drinks,Books,Snacks) as LowestOrders format= comma16.,
       mean(Assorted,Misc_Pack,Candy,Drinks,Books,Snacks) as AverageOrders format= comma16.
     from spl_transposed;             
quit;

Question:

How can the program be modified to avoid typing out all column names in the summary functions?

DICTIONARY.columns combined with data driven macro variables can be used. Select the name column only and put the column names in a macro variable. Use the SEPARATED BY option to separate the values with commas. Specify the library and table name.

proc sql;
select name
     into :colNames separated by ","
     from dictionary.columns
     where libname="WORK" and memname="SPL_TRANSPOSED";
quit;

%put &colNames;

ColNames is a macro variable with all column names from the spl_transposed table. Copy the variable list from the log and paste it in a new macro variable. Delete state_region_nm from the list as it is not a product line.

%let pl=Assorted,Misc_Pack,Candy,Drinks,Books,Snacks;

%put &=pl;

The macro variable PL can be used in the summary functions:

proc sql;
select state_region_nm label= "State Name",
       max(&pl) as HighestOrders format= comma16.,
       min(&pl) as LowestOrders format= comma16.,
       mean(&pl) as AverageOrders format= comma16.
     from spl_transposed;             
quit;

Troubleshooting Macros

Macro variable scope can cause subtle bugs- especially when variables with the same name exist. Utilize DICTIONARY.macros to investigate.

There are four columns in DICTIONARY.macros that can be viewed with the DESCRIBE TABLE statement.

proc sql;
describe table dictionary.macros;
quit;

Let's say the states from spl_transposed are put into macro variables named Staten. %PUT prints defined macro variables to the log, showing there are 22 states.

proc sql;
select state_region_nm
     into :State1-
     from spl_transposed;
quit;

%PUT _USER_;

Then, a macro is written to print a report of the total orders in each product line for each state. However, there's an issue:

%macro StateProductLines;

     %do i=1 %to 22;

          %local i;
          %let i=10;

          title "Total Orders by Product Line in &&state&i";
          proc print data=spl_transposed noobs label;
               where state_region_nm="&&state&i";
               label state_region_nm="State Name";
          run;
          title;

     %end;

%mend;

%StateProductLines

Partial results:

All 22 reports print with Minnesota's orders.

Question:

Why is the macro not printing a report for each state?

The issue is %local i and %let i=10 reset the variable i inside the loop. While this may be more obvious in a small program, a small detail like this could get lost in a more detailed macro program. To debug, use DICTIONARY.macros inside the macro DO loop in the macro definition to print all macros to the log.

%macro StateProductLines;

     %do i=1 %to 22;

          %local i;
          %let i=10;

          title "Total Orders by Product Line in &&state&i";
          proc print data=spl_transposed noobs label;
               where state_region_nm="&&state&i";
               label state_region_nm="State Name";
          run;
          title;

          /* Prints all macros to the log */
          proc sql;
          select *
               from dictionary.macros;
          quit;

     %end;

%mend;

%StateProductLines

Partial results:

This confirms that i is defined locally in the StateProductLines macro with a value of 10. To fix the issue, delete the local i and %let i=10 statements.

%macro StateProductLines;

     %do i=1 %to 12;
            
          title "Total Orders by Product Line in &&state&i";
          proc print data=spl_transposed noobs label;
               where state_region_nm="&&state&i";
               label state_region_nm="State Name";
          run;
          title;

     %end;

%mend;

%StateProductLines

Partial results:

The macro now runs as expected.

In Summary

Dictionary tables are a hidden gem in SAS that can be used a number of ways.

In this post, we explored how:

DICTIONARY.tables can be used to explore datasets in a library.
DICTIONARY.columns helps identify common columns for joins.
DICTIONARY.columns combined with data driven macro variables generates a list of column names.
DICTIONARY.macros can assist in troubleshooting macros

How do you use dictionary tables in your SAS programs? Share your tips and tricks in the comments!

Code to create spl_transposed:

The following code uses the table customer_orders_items created in the previous inner join to create spl_transposed.

proc sql;
create table StateProductLines as
select distinct state_region_nm, product_line, count(product_line) as productlinetotal format= comma20.
     from customer_orders_items
     where state_region_nm is not null
     group by state_region_nm, product_line
     order by state_region_nm, productlinetotal desc;
quit;

proc transpose data=stateproductlines 
               out=spl_transposed(rename=("Misc Pack"n=Misc_Pack) drop=_name_);
     var productlinetotal;
     id product_line;
     by state_region_nm;
run;

proc sql;
     update spl_transposed
     set
          Snacks= coalesce(Snacks, 0);
quit;

If you are primarily a DATA step programmer, note there are equivalent views in the SASHELP library. For example, SASHELP.vcolumn is the equivalent to DICTIONARY.columns.

Find more articles from SAS Global Enablement and Learning here.