BookmarkSubscribeRSS Feed

Library Datasets Summary Macro %DATA_SPECS

Started ‎03-23-2019 by
Modified ‎07-14-2020 by
Views 6,674

In preparations for SAS Global Forum 2019 I am putting together an article reference for my presentation as well as uploading the macro so others can try using it.  This macro has been extremely helpful for myself in the various meta and pooled analyses that I work on.  I receive data from a large number of oncology research groups and have to be able to see what's in it quickly, sometimes without a data dictionary.  The DATA_SPECS macro summarizes all of the datasets and variables contained within a specific library and creates and Excel report in a data dictionary style format.



The field of clinical research often involves sharing data with other research groups and receiving data from other research groups.  This creates the need to have a quick and concise way to summarize incoming or outgoing data that allows the user to get a grasp of the number of datasets, number of variables, and number of observations included in the library as well as the specifics of each variable within each dataset.  The CONTENTS procedure can fulfill this role to an extent, but the DATA_SPECS macro uses the REPORT procedure along with the Excel Output Delivery System (ODS) destination to create a report that is fine tuned to summarize a library.  The macro produces a one page overview of the datasets included in the specified library, and then creates a new worksheet for each dataset that lists all of the variables within that dataset along with their labels, formats, and a short distribution summary that varies depending on variable type.  This gives the user an overview of the data that can be used in documents such as data dictionaries, and demonstrates an example of the powerful reports that can be generated with the ODS Excel destination.


What does the macro do?

Summarize a Library of Datasets

The DATA_SPECS macro is designed to give the user a quick but informative look at a library of datasets.  The generated report creates three different types of tables.

Table 1: Summary of datasets


The first table is a simple listing of the datasets that are contained within the specified library.  There is a column for the dataset name (which includes any dataset label in parentheses), number of observations, number of unique index values, and number of variables.  Index variables are specified to the macro to try to determine unique subjects and can be variables such as patient ID.  Knowing the number of observations vs the number of unique index values indicates to the user if the dataset has one row per subject or multiple observations per subject. 


Table 2: Summary of variables that exist in multiple datasets


The second table will display any variables that exist across multiple datasets in the library.  This information is important to see if any critical variables such as gender have different values for the same subject across datasets.  The table lists the variable name, the list of datasets it is contained in, and all unique variable labels that variable has in those datasets.  Different labels can indicate the variable has different meanings in the different datasets.


Table 3: Summary of variables within each dataset

The macro creates one table within a worksheet for each dataset in the library which contains a summary of each variable within that dataset.  The variables can be laid out in three different formats: long, condensed, and wide.  The meta-data for each variable includes its name, label, format, length, type, and a short distribution that varies depending on the type of variable.


Long format version:


In this version only two columns are used in the worksheet.  The first indicates which piece of meta-data on the variable is being listed, and the second has the corresponding value.


Condensed format version:


In this version there is one row for each variable, and each of the meta-data pieces has their own column.


Wide format version:


In this version the variables are listed left to right instead of up to down.  There is one row for each piece of meta-data and one column for every variable.


Numeric Variables:

Numeric variables are summarized in one of two ways depending on the macro parameter CAT_THRESHOLD.  If there are more unique values than the threshold specified by CAT_THRESHOLD then the variable is considered continuous and is summarized with the number of non-missing observations, number of missing observations, median and range.  The format attached to the variable is used for the median and range to accommodate date type variables.  



If there are less than or equal to the CAT_THRESHOLD unique items then the variable is considered discrete.  Discrete variables are shown with a frequency list of each value in the variable.  If the variable is formatted and the formatted value does not match the unformatted value, then the list is shown as UNFORMATTED (FORMATTED): N (%).


Character Variables:

Character variables are always considered to be discrete variables by the macro, but the CAT_THRESHOLD parameter is again used to determine if the variable has too many unique levels to effectively be shown.  If there are more levels than CAT_THRESHOLD then the macro simply lists how many missing and non-missing values there are.





How to use the macro?

The DATA_SPECS macro is simple to use and is very thoroughly developed for sharing.  The macro has customizability with few parameters, full documentation, and full error checking on the parameters.  The program will run without changing the user’s settings, leaves behind no temporary datasets, and does not put any output to the log or results windows.


Macro Parameters

The DATA_SPECS macro only has two required parameters and five optional parameters.  The required parameters are the only options that require inputs when calling the macro as the optional parameters all have default settings.


Required Parameters


The LIBN parameter refers to the libname of interest to be summarized by the macro.  The library must exist and the libname must be established prior to running the macro. 



The OUT parameter refers to the outputted Excel file that is created at the end of the macro that will contain the library summary.  The file path should include the .XLSX at the end of the filename and requires the full path.


Optional Parameters


The INDEX parameter allows the user to specify any potential patient identifying variables that will be used by the macro to determine how many unique patients or subjects are within each dataset if the variable(s) are within the dataset.  Multiple variables can be specified in two different ways.  If there are multiple index variables, such as patient ID and study center ID, they can be specified in a list separated by spaces.  The macro will search through the list and find all of the unique combinations of the listed variable as a new combined INDEX variable.  The second scenario is that different datasets have different patient ID variables.  This often happens when multiple data transfers happen from the same group, but by different programmers.  This can be accommodated by the macro by separating lists with a | (pipe) symbol. The macro will search each dataset for the first of the separated lists that exists and use that to create the index variable.



The CAT_THRESHOLD parameter determines how many unique values a numeric variable can have before it is considered continuous.  When the number of distinct values is less than the threshold then a frequency distribution will be shown in the summary.  Otherwise continuous statistics such as median and range are shown in the summary.



The WHERE parameter allows the user to pass a WHERE statement into the dataset produced by the CONTENTS procedure within the macro.  This can be useful if certain variables or datasets are not wanted within the summary.



The FORMAT parameter determines whether the variables within each dataset’s summary are shown in a long or wide format.  The long format will list all of the variables vertically, and the wide format will list all of the variables horizontally.



The ORDER parameter determines whether the variables within each dataset are shown alphabetically or by the variable order within the original dataset.


Error checking

The DATA_SPECS macro has full error checking programmed in order to improve the user experience.  The macro will check the following list of issues:

  1. If the library exists and has been assigned
  2. If the OUT and LIBN parameters are missing in the macro call or set to null
  3. If the CAT_THRESHOLD parameter is not a number greater than 0
  4. If the FORMAT parameter is not set to a value of LONG or WIDE
  5. If the ORDER parameter is not set to a value of VARNUM or ALPHA
  6. If the current session’s SAS version is not at least 9.4 or greater

If any issues are found the macro will do the following:

  1. Stop the macro and restore any changed options
  2. Send an error message to the log that describes the issue and gives potential solutions. For example if using the wrong value for the FORMAT parameter the macro will provide the list of acceptable values in the log.


The DATA_SPECS macro is a powerful tool for quickly summarizing a library of datasets.  The ability to quickly determine the number and size of datasets as well as have a quick glance at the content of the variables within each dataset will increase the efficiency of understanding and integrating new or shared data.  The DATA_SPECS macro is also an excellent example of the versatile reports that can be created using the Excel output destination.  Being able to create tables within separate tabs allows for quick navigation, and not having vertical or horizontal space limits allows for more information to be presented within a single table.  The Excel output destination still has the useful options that TAGSETS.EXCELXP did, but with the benefits of smaller file sizes and more flexible customizations.



The following is an example using the SAS library SASHELP which is automatically created when opening SAS.  Some datasets are excluded due the sheer number of variables. The outputted document is attached to this article.


%data_specs(LIBN=sashelp, OUT=data_specs_example.xlsx,
    INDEX=id, WHERE=memname ^in('LEUTEST' 'LEUTRAIN') 
                               and substr(memname,1,1)^='V');



Hi Jeff,


Thanks for sharing this macro - I can already see myself using it regularly in future.


I have just one suggestion for improvement. For Table 3 (Summary of variables within each dataset), in addition to LONG and WIDE, is it possible to add a third option so that there is one variable per row, and four columns (one for each specification)? This would make the output consistent with the other tables, and facilitate quickly finding a variable of interest.


Thanks again, and I look forward to seeing your final SAS Global Forum 2019 presentation.



Hello @Norman21,

That is a great suggestion and was actually quite simple to implement. Please see the additional option CONDENSED to the parameter FORMAT that outputs like the following screenshot:



I updated the downloadable macro on this webpage to include the option.


Thanks again for the feedback!


Why does it report that NAME has a format attached of $18.? When I run PROC CONTENTS on SASHELP.BASEBALL there is no format attached to the variable NAME.


Hello @Tom,

When a character variable does not have an actual format applied to it the default format it has is the length ($length to be exact). You can see this with the VFORMAT function:

data _null_;
    set (obs=1);
    put x=;


'library at datasets'?  Is that meant to be 'library of datasets' or something else?


So that is a limitation of using the VFORMAT() function instead of querying the metadata (via PROC CONTENTS or the DICTIONARY.COLUMNS view).  So for numbers without a format attached it returns `best12.` and for character variables it returns `$` format with a width that matches the length of the variable.

@tomrvincent thank you for pointing that out for me. It is supposed to be "library of datasets." I made the update.



@Tom I personally don't consider it a limitation for the following reason. When I'm making a macro variable that saves the format that I later need to use in a PUT function the VFORMAT function creates the exact value that I need including the period. If I pull from the dictionary tables or proc contents output table then I have to add more logic if there's no format listed.

When I write "index=sex age" , I can't find these two variables in Table 2. But when I write "index=sex|age"  they appeared again.

May I ask about the role of the "|"? Thanks~

@Oleander_ the | symbol is what the macro uses as a delimiter between variable names. I could probably just use spaces but when I wrote the macro I was still fairly new and now the option has been established at my work unit and is hard to change due to existing programs.


when I use the macro and run it using this statement I get an error.




how would I resolve this error?


ERROR: The text expression length (-30995) exceeds maximum length (65534). The text expression has been truncated to 65534
ERROR:  An exception has been encountered.
Please contact technical support and provide them with the following traceback information:
The SAS task name is [Submit]
ERROR:  Write Access Violation Submit
Exception occurred at (0599CE8C)
Task Tracebac

Hello @VDD,

   I would not recommend running the macro on the entire SASHELP library.  There are a few data sets and SQL views that have an enormous number of variables and observations and I believe it is overloading the macro in your case.

Nice contribution @JeffMeyers.

Will save time in data prep for curat.


Version history
Last update:
‎07-14-2020 12:50 PM
Updated by:



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags