A published table often includes counts in the column headers to provide additional information about the number of observations that contribute to a specific column. You can add the counts using brute force by running a procedure in advance to get the numbers and then hard coding the value in a label or format. However, this blog explores a more elegant solution, which is to dynamically add the counts by using DATA step logic and the FORMAT procedure.

Note: Each section describes an aspect of this solution. Refer to the section at the end of the blog for example code.

Creating the data set

The sample code in this blog creates a subset of the Sashelp.Cars data set. To add counts in the headings, complete the following steps.

First, run the FREQ procedure on the desired variable to get the counts and store the results in an output data set.

You can then use the output data set in a subsequent DATA step to concatenate the value of the variable with the corresponding count.

The concatenated value also includes the forward slash (/) character, which is the default split character for headings in the REPORT procedure. This character allows the text for the count to appear on the second line of the heading.

Building the format with column header counts

Now that you have obtained the required variables, you can build the format in the next DATA step.

To create a user-defined format from data values, you must assign the following variables:

• START: Contains the internal values for which you are creating the format
• LABEL: Contains the values that you want to assign as the formatted values
• FMTNAME: Names the format that you are creating

Note: If you create a character format, you must precede the name with a dollar sign ($) character or add the TYPE variable and assign a value of C.

Next, in a PROC FORMAT step, assign the data set that you just created on the CNTLIN option.

Then, use the FMTLIB option to display the results of the format that you created to the active output window. An optional SELECT statement selects only the format that you want to see.

The following are the FMTLIB option results:

These results show the internal values and the formatted labels that are assigned to those values.

Using the format

Finally, use the format on the ACROSS variable in PROC REPORT.

Here is the full code:

/* Create sample data. */
data cars;
set sashelp.cars;
where make in ('Honda','Toyota','Ford','BMW');
keep make type msrp;
run;
 
/* Get counts for the header. */
proc freq data=cars;
tables make / out=counts(drop=percent);
run;
 
/* Combine variable with counts and the split (/) character. */
data counts;
set counts;
header=catt(make,'/','(N=',count,')');
run;
 
/* Build the format. */
data build_format;
set counts;
start=make;
label=header;
fmtname='$cnt_fmt';
run;
 
proc format cntlin=build_format fmtlib;
select $cnt_fmt;
run;
 
ods _all_ close;
title;
options missing='0';
ods rtf file='test.rtf';
 
/* Use the format on the ACROSS variable. */
proc report data=cars;
column ('Style of car' type) make,msrp;
define type / group ' ';
define make / across 'Manufacturer' format=$cnt_fmt.;
define msrp / sum ' ';
run;
 
ods rtf close;
ods listing;

The code generates the following output table:

As you can see, the column headers contain counts that make it easier to understand the results.

Summary

The technique described above demonstrates how to dynamically include counts in headers in an output table. Whether the counts in the headers are a requirement for your report or the counts are a feature that you choose to include, this solution enhances your report and provides the reader with additional, relevant information.