Creating a report that displays only the column headings for a data set containing 0 records

This post was kindly contributed by SAS Users - go there to comment and to read the full post.

A customer recently contacted SAS Technical Support and wanted to know how he could generate a report that displays just the column headings for a data set (or table) that does not contain any records. Rather than just omitting the missing data, he wanted to provide his customers with a visual way to see where data was missing.

This blog demonstrates how to create a report that provides only the column headings for data that is missing. The blog also explains how to create, select, and exclude output objects as well as how to generate reports with the SAS® Output Delivery System (ODS). These concepts are relevant to the task of generating a report with the column headings for a data set that contains no (0) observations.

The first section below provides some basic information that you need to understand about ODS. Specifically, it discusses ODS destinations along with the concept of output objects and how they work in ODS.

The second section explains tools that enable you to achieve the desired output for the report:

how to use the ODS SELECT statement to specify certain objects that you want to send to the destination for the report.
how to use a SAS® macro and dictionary tables to display column headings when no output object is generated

The last section provides a code example that unites all of these concepts. The end result is a report that contains only column headings from the WORK.CLASS data set and information from the Moments output object in the SASHELP.CLASS data set.

Understanding the SAS® Output Delivery System

The SAS Output Delivery System has many destinations that you can use to generate files in various formats. Some of these destinations generate files in third-party formats such as XLSX (Excel), DOCX (Word), PPTX (PowerPoint), and HTML (HTML, HTML5). Other types of destinations are available, too. Examples include ODS Package, which generates Archive (or ZIP) files, and ODS Document, which generates binary objects from SAS DATA steps or procedures.

The foundation for the output delivery system is an output object, which is generated when a SAS® procedure or DATA step is executed. The output object is generated when you combine text and numbers with a template definition.

DATA steps generate only one output object, whereas procedures can generate one or more output objects.

Using ODS statements to select or exclude output objects

To see the contents of an output object, you can use the ODS TRACE statement to generate trace records. A trace record displays the object name, the template location, and the label.

The following example generates trace records for the SASHELP.CLASS data set:

ods trace on;
univariate data=sashelp.class;
run;

The output from this code is shown below:

Once you discover an object’s name, you can choose to select or exclude it from the output by using either the ODS SELECT statement or the ODS EXCLUDE statement.

For example, using the previous code example, you can use the ODS SELECT statement to choose a specific output object.

ods trace on;
ods select moments;
proc univariate data=sashelp.class;
run;

In this example, The ODS SELECT statement selects just the Moments object and sends it to any open ODS destinations so that the object’s data can be printed in a report. No other objects are sent to the destination.

The ODS TRACE statement generates the following output in the trace log since only the Moments object is specified.

Generating a report with column headings for a data set with 0 records and with information from a selected output object

The information in the previous section is helpful for data sets that contain records. But it is not helpful if you want to generate a report that shows column headings from a data set that does not have any records.

When a table has no records, it does not generate an output object. Because no object is generated, ODS cannot display headings.

However, you can use another strategy with ODS to display headings from a data set with no records. You can use dictionary tables to obtain the name of the column headings for a table that has no records. Dictionary tables are created automatically by SAS® to store information related to SAS libraries, SAS system options, SAS catalog, and so on. These tables enable you to query information about a data set (column names, titles, and so on).

To accomplish the task at hand, you can use a dictionary table and a SAS macro with ODS. The macro is used to verify whether you are processing a zero-observation data set:

If the data set that is passed is not a zero-observation data set, the macro passes and executes the procedure.
If the data set that is passed is a zero-observation table, the SQL procedure is used with the DICTIONARY.COLUMNS table to query only the column names in the table. Then, the column names are transposed with the TRANSPOSE procedure, and they are displayed with the REPORT procedure.

Note: The column headings are arranged vertically. You need to transpose them so that they are displayed horizontally in the report that is generated by the ODS destination.

Creating the report with SAS® Output Delivery System, a SAS® macro, and dictionary tables

The following example illustrates the strategy that is described in the last section:

/* Sample table with 0 observations */
data work.class;
set sashelp.class;
stop;
run;
ods excel file="sample.xlsx" options(embedded_titles="yes");
 
%macro test(libref=,dsn=);
%let rc=%sysfunc(open(&amp;libref..&amp;dsn,i));
%let nobs=%sysfunc(attrn(&amp;rc,NOBS));
%let close=%sysfunc(CLOSE(&amp;rc));
%if &amp;nobs ne 0 and %sysfunc(Exist(&amp;libref..&amp;dsn)) %then %do;
 
title "Report for Company XYZ";
ods select moments;
proc univariate data=&amp;libref..&amp;dsn;
run;
 
%end;
%else %do;
 
proc sql noprint;
create table temp as
select name from dictionary.columns
where libname=%upcase("&amp;libref") and memname=%upcase("&amp;dsn");
run;
quit;
proc transpose data=temp out=temp1(drop=_label_ _name_);
id name;
var name;
run;
proc report noheader style(column)=header[just=center] nowd;
title "No data for data set &amp;libref..&amp;dsn";
run;
%end;
%mend;
%test(libref=work,dsn=class)
%test(libref=sashelp,dsn=class)
 
ods excel close;

As you can see below, the report that is generated shows the column headings for the empty WORK.CLASS data set as well as the data from the Moments object from the SASHELP.CLASS data set.