The SAS language is large. Even after 20+ years of using SAS, there are many features that I have never used. Recently it became necessary for me to learn about DICTIONARY tables in PROC SQL (and the associated SASHELP views) because I needed to programmatically obtain the text for the current value of the system title in SAS. I had heard a lot about DICTIONARY tables, but this was my first time using them in a program.
This article discusses DICTIONARY tables and shows how to use the DICTIONARY.Titles table to obtain the current value of titles and footnotes in SAS.
DICTIONARY tables and titles
DICTIONARY tables are documented in the PROC SQL documentation. They are special read-only PROC SQL tables that contain information about the current state of SAS, including the state of libraries, data sets, and system options. The documentation lists all DICTIONARY tables, and I determined that I needed to look at the DICTIONARY.Titles table in PROC SQL or (if I needed to use another SAS procedure) the SASHELP.VTitle view, which contains the same information.
After finding out which table to use, I wanted to display the contents of the table. The following call to PROC SQL displays the structure of the table (names and types of variables) and the contents. Equivalently, you can use PROC CONTENTS and PROC PRINT to show similar information for the view SASHELP.VTitle. The output shows the table for a new SAS session:
proc sql; describe table Dictionary.Titles; /* writes to SAS log */ select * from Dictionary.Titles; /* display table */ quit; proc contents data=Sashelp.VTitle; run; proc print data=Sashelp.VTitle; run;
The table contains three variables:
- The TYPE variable is a one-character variable with the values 'T' (for title) or 'F' (for footnote).
- The NUMBER variable is a numeric variable. The value '1' indicates the value of the TITLE1 or FOOTNOTE1 global statements. The value '2' indicates the value of the TITLE2 or FOOTNOTE2 statements, and so on.
- The TEXT variable is a 256-character variable that contains the value of a title or footnote. The output shows that when you first start SAS, the TITLE1 statement is set to "The SAS System."
You can run an example to see how the contents of the view change after you submit TITLE and FOOTNOTE statements:
title "Normal Distribution"; /* alias for TITLE1 statement */ title2 "mu = 0; sigma = 1;"; footnote "N = 100"; /* alias for FOOTNOTE1 statement */ proc print data=Sashelp.VTitle; run;
Putting a title into a macro variable
The structure of the DICTIONARY.Titles table implies that you can use a WHERE clause to subset the table. For example, the clause WHERE Type="T" & Number=1 selects only the row for the TITLE1 statement. Similarly, the clause WHERE Type="F" & Number=2 selects the row for the FOOTNOTE2 statement. You can use the SELECT INTO :MacroVar statement in PROC SQL to put data into a macro variable, as follows:
PROC SQL noprint; select Text into :TitleText TRIMMED /* put the trimmed value into a macro */ from Dictionary.Titles where Type="T" & Number=1; quit; %put &=TitleText;
You need to be a little careful when using this technique in production code. If you ask for the TITLE2 information when that title is not set, then the WHERE clause will return an empty table. For example, if you clear the TITLE1 statement and rerun the previous PROC SQL statement you will see that the SAS log displays NOTE: No rows were selected and value of the TitleText macro is not updated.
One way to handle this potential problem is to use the %LET statement to set the macro variable to an empty value before you call PROC SQL. If the macro variable is empty after PROC SQL runs, then the requested title or footnote is not set. The following macro uses this technique to set a macro variable to the value of the Nth title, where you can specify the parameter N:
/* Get the N_th title into the &TitleText macro variable */ %macro GetTitle(Number=1); %global TitleText; %let TitleText = ; /* value is empty if TITLEn does not exist */ PROC SQL noprint; select Text into :TitleText TRIMMED /* value is set if TITLEn exists */ from Dictionary.Titles where Type="T" & Number=&Number; quit; %mend; /* test the %GetTitle macro */ title "Lognormal Distribution"; /* set TITLE1; clear TITLE2 */ %GetTitle(); %put Title1 = "&TitleText"; /* text of title1 */ %GetTitle(Number=2); %put Title2 = "&TitleText"; /* text of title1 */
Title1 = "Lognormal Distribution" Title2 = ""
This basic macro is sufficient for my purposes. Feel free to propose improvements in the comments. Also, let me know how you use DICTIONARY tables in your work.
If you would like to learn more about DICTIONARY tables, the following two references will get you started. Many papers have been written about DICTIONARY tables and views. An internet search of the form
sas proceedings "dictionary table"
will reveal some of the papers from SAS conferences.
- Eberhardt, P. and Brill, I. (2006) "An Introduction to SAS Dictionary Tables"
- SAS documentation for PROC SQL, "Accessing SAS System Information By Using DICTIONARY Tables"