SAS Macros for Clinical Table Generation

Introduction

Clinical trial reporting rarely consists of producing one table one time. A typical study may require dozens or hundreds of tables whose structure is similar but whose population, variables, treatment groups, statistics, titles, footnotes, and analysis datasets differ.

This is where SAS macros become particularly valuable. Instead of writing a separate PROC SQL, PROC FREQ, PROC MEANS, and PROC REPORT program for every table, a programmer can encapsulate recurring logic in a parameterized macro and reuse that logic across many outputs.

The objective is not simply to make SAS code shorter. A well-designed macro creates a controlled reporting component that is easier to reuse, review, test, document, and maintain.

What Is a SAS Macro?

The SAS macro facility is a text-generation and programming mechanism that allows SAS code to be generated dynamically.

A simple macro can look like this:

%macro hello;
    %put Hello from the SAS macro facility.;
%mend hello;

%hello;

The macro processor resolves the macro invocation and generates the SAS statements needed for execution.

For clinical programming, the more important capability is parameterization. A macro can accept information from the calling program and insert that information into reusable SAS code.

%macro print_data(
    data=
);

    proc print data=&data;
    run;

%mend print_data;

%print_data(data=adam.adsl);

Why Macros Matter in Clinical TLF Production

Clinical tables, listings, and figures often share common programming patterns. For example, many tables require:

• a treatment variable;
• one or more analysis populations;
• formatted treatment labels;
• descriptive statistics;
• consistent decimal precision;
• missing-value handling;
• population denominators;
• titles and footnotes;
• and a standardized ODS output destination.

If these features are independently coded in every program, small differences can accumulate across the reporting package.

Positional Versus Keyword Parameters

SAS macros support both positional and keyword parameters.

Positional parameters

%macro summarize(data, variable);

    proc means data=&data;
        var &variable;
    run;

%mend summarize;

%summarize(adam.adlb, aval);

The calling sequence determines which value is assigned to each parameter. This approach is compact but can become difficult to maintain when a macro has many parameters.

Keyword parameters

For clinical reporting macros, keyword parameters are usually easier to read.

%macro summarize(
    data=,
    variable=,
    class=
);

    proc means data=&data;
        %if %length(&class) %then %do;
            class &class;
        %end;
        var &variable;
    run;

%mend summarize;

%summarize(
    data=adam.adlb,
    variable=aval,
    class=trt01an
);

Designing a Clinical Table Macro

Consider a common clinical table showing the number and percentage of subjects experiencing at least one adverse event.

The desired table might contain:

The table-generating macro should not hard-code these treatment groups. Instead, it should receive the relevant treatment variable and population information as parameters.

A Basic Treatment-Group Macro

A useful starting point is a macro that accepts a dataset, treatment variable, and population variable.

%macro make_ae_table(
    data=,
    trtvar=,
    where=
);

    proc freq data=&data;

        %if %length(&where) %then %do;
            where &where;
        %end;

        tables &trtvar / missing;

    run;

%mend make_ae_table;

The important point is that the macro does not know whether the caller is using ADSL, ADAE, a treatment-emergent population, or another analysis dataset. That information is supplied by the calling program.

Adding Population Control

Clinical tables frequently use analysis-population flags such as SAFFL, ITTFL, or FASFL.

Rather than embedding a particular population flag inside the macro, make the population expression configurable.

%macro make_ae_table(
    data=,
    trtvar=,
    popvar=,
    popval=Y
);

    proc freq data=&data;

        where &popvar="&popval";

        tables &trtvar / missing;

    run;

%mend make_ae_table;

%make_ae_table(
    data=adam.adae,
    trtvar=trt01an,
    popvar=SAFFL
);

Now the same macro can be used with a different population without changing its internal logic.

Macro Variables and Indirect References

One of the most useful techniques in macro-driven reporting is using macro variables to control the values inserted into generated SAS code.

%let analysis_ds = adam.adae;
%let treatment   = trt01an;
%let population  = SAFFL;

%make_ae_table(
    data=&analysis_ds,
    trtvar=&treatment,
    popvar=&population
);

This approach can be extended to control titles, footnotes, output paths, study identifiers, treatment labels, and analysis parameters.

Building a Reusable Summary Component

A production macro often needs to generate a standardized count and percentage.

For a treatment group with denominator \(N_j\), the percentage for a category with count \(n_j\) is:

The important programming issue is not the formula itself but ensuring that the correct denominator is used for every treatment group and population.

Creating a Count-and-Percentage String

A common reporting convention is to display the count and percentage in one cell, such as 61 (61.0%).

length result $40;

if denominator > 0 then
    result = cats(
        put(count, 8.),
        ' (',
        put(100*count/denominator, 5.1),
        '%)'
    );
else
    result = '0 (0.0%)';

In production programming, the formatting rule should generally be centralized rather than repeated independently throughout every table program.

Using PROC REPORT

PROC REPORT is particularly useful for clinical tables because it provides substantial control over column structure, labels, ordering, statistics, and ODS output.

proc report data=final_table nowd split='|';

    column parameter placebo drug_a drug_b total;

    define parameter / display
        "Parameter"
        style(column)=[cellwidth=2.2in];

    define placebo / display
        "Placebo|(N=100)";

    define drug_a / display
        "Drug A|(N=102)";

    define drug_b / display
        "Drug B|(N=101)";

    define total / display
        "Total|(N=303)";

run;

The macro can generate the surrounding PROC REPORT structure while the table-specific program supplies the dataset and column configuration.

Dynamic Treatment Headers

A common challenge is that treatment labels and denominators change from study to study.

Instead of hard-coding:

define placebo / display "Placebo|(N=100)";
define drug_a  / display "Drug A|(N=102)";

the labels can be constructed dynamically.

%let trt1_label = Placebo;
%let trt1_n     = 100;

%let trt2_label = Drug A;
%let trt2_n     = 102;

define trt1 / display
    "&trt1_label|(N=&trt1_n)";

define trt2 / display
    "&trt2_label|(N=&trt2_n)";

This becomes particularly powerful when combined with standardized treatment metadata.

Using Metadata to Drive the Table

A mature TLF framework can maintain study-specific treatment metadata in a control dataset rather than embedding treatment labels throughout the code.

The macro can then retrieve the treatment labels and use them to construct the final report headers.

Macro Loops

Macro loops are useful when the same block of generated SAS code must be repeated for multiple treatment groups or analysis variables.

%macro treatment_columns(ntrt=3);

    %do i=1 %to &ntrt;

        define trt&i / display
            "Treatment &i";

    %end;

%mend treatment_columns;

%treatment_columns(ntrt=3);

The macro processor generates the repeated SAS statements before the SAS compiler executes them.

Conditional Macro Logic

Clinical tables frequently require optional features. For example, a table may contain a Total column in one study but not another.

%macro table_columns(
    total=yes
);

    define placebo / display "Placebo";
    define drug_a  / display "Drug A";

    %if %upcase(&total)=YES %then %do;

        define total / display "Total";

    %end;

%mend table_columns;

This allows one macro to support multiple standardized table configurations without creating unrelated versions of the same program.

Generating Titles and Footnotes

Titles and footnotes are part of the clinical output specification and should be treated as controlled metadata rather than informal text.

%macro table_header(
    number=,
    title=,
    population=
);

title1 "Table &number";
title2 "&title";
footnote1 "Population: &population";

%mend table_header;

%table_header(
    number=14.3.1,
    title=Treatment-Emergent Adverse Events,
    population=Safety Population
);

Combining Macros Into a TLF Framework

A useful clinical reporting framework separates responsibilities among several macros.

A More Complete Clinical Table Macro

The following example illustrates the architecture of a reusable table macro. It intentionally separates configuration from reporting mechanics.

%macro clinical_table(
    data=,
    where=,
    treatment=,
    variable=,
    title=,
    footnote=,
    outfile=
);

    %local condition;

    %if %length(&where) %then
        %let condition = where &where;;

    proc sort data=&data out=_tbl_input;
        by &treatment;
        &condition
    run;

    proc freq data=_tbl_input noprint;

        tables &treatment*&variable /
            out=_tbl_freq
            missing;

    run;

    ods rtf file="&outfile";

    title "&title";

    proc report data=_tbl_freq nowd;

        column &treatment &variable count percent;

        define &treatment / group "Treatment";
        define &variable  / group "Category";
        define count       / display "n";
        define percent     / display "Percent";

    run;

    footnote "&footnote";

    ods rtf close;

    proc datasets library=work nolist;
        delete _tbl_input _tbl_freq;
    quit;

%mend clinical_table;

This example is intentionally simplified. A production implementation would typically require more extensive handling of treatment metadata, denominators, formats, ordering, missing categories, output paths, titles, footnotes, logging, and validation.

Why Temporary Datasets Matter

Reusable macros often create intermediate datasets. These datasets should have controlled names and should not accidentally overwrite permanent study data.

A common convention is to use temporary WORK datasets:

_tbl_input
_tbl_freq
_tbl_summary
_tbl_final

At the end of the macro, temporary objects should be removed when appropriate.

proc datasets library=work nolist;
    delete
        _tbl_input
        _tbl_freq
        _tbl_summary
        _tbl_final;
quit;

Macro Variable Scope

Macro variable scope becomes increasingly important as the reporting framework grows.

A macro variable created with %let may have local or global implications depending on where it is created and whether the macro facility finds an existing symbol table.

For reusable production macros, explicitly declaring local variables helps prevent unintended interaction with the calling program.

%macro example(
    data=
);

    %local condition i result;

    /* Macro logic */

%mend example;

Handling Optional Parameters

A well-designed macro should provide sensible defaults whenever a parameter has a natural default.

%macro report_table(
    data=,
    treatment=trt01an,
    decimals=1,
    total=yes
);

    /* Reporting logic */

%mend report_table;

The caller can therefore specify only the information that differs from the standard configuration.

%report_table(
    data=adam.adae
);

Macro Validation

Macro validation is more than checking whether the generated SAS program runs. A macro can execute without errors and still produce a clinically incorrect table.

Validation should therefore address at least four levels:

Inspecting the Generated SAS Code

One of the most important debugging techniques is to inspect the code generated by the macro processor.

The SAS options MPRINT, MLOGIC, and SYMBOLGEN are particularly useful during development.

options mprint mlogic symbolgen;

For example, MPRINT shows the SAS statements generated by a macro, while SYMBOLGEN can show how macro variable references resolve.

Common Macro Failure Modes

1. Unquoted special characters

Macro parameters containing special characters may require careful handling, particularly when they are inserted into WHERE expressions or generated code.

2. Unexpected macro resolution

Text containing ampersands or percent signs can be interpreted by the macro processor rather than treated as literal text.

3. Incorrect denominator logic

This is one of the most consequential clinical reporting errors. A table may look visually correct while using an inappropriate denominator.

4. Treatment ordering errors

Treatment columns should follow the approved study order rather than alphabetical order unless alphabetical ordering is explicitly intended.

5. Hidden dependencies

A macro that assumes a global macro variable, format catalog, library, option, or dataset exists can become difficult to reuse safely.

Macro Design: What Should Be Parameterized?

A useful rule is to parameterize information that legitimately varies while keeping the core reporting algorithm stable.

What Should Usually Not Be Parameterized?

Over-parameterization can make a macro harder to understand than the individual programs it was designed to replace.

If every statement can be turned on or off through a parameter, the macro may become a configuration language rather than a reusable reporting component.

A Practical Table-Generation Architecture

A mature clinical programming environment can be organized into layers.

Example: Calling a Production-Style Macro

The final table program should ideally be relatively short because most of the reusable programming logic has already been validated.

%clinical_table(
    data      = adam.adae,
    where     = saffl="Y" and trtemfl="Y",
    treatment = trt01an,
    variable  = aedecod,
    title     = Treatment-Emergent Adverse Events by Preferred Term,
    footnote  = Safety population; treatment-emergent events,
    outfile   = "&outdir./table_14_3_1.rtf"
);

This style makes the purpose of the program immediately visible. The caller defines what should be produced; the macro controls how the standardized table is generated.

Macro Libraries in Clinical Programming

Large clinical programming groups often maintain reusable macro libraries rather than embedding all macro definitions inside individual table programs.

A library might contain components for:

• population filtering;
• treatment metadata;
• count and percentage formatting;
• continuous descriptive statistics;
• categorical summaries;
• confidence intervals;
• p-values;
• PROC REPORT layouts;
• ODS RTF and PDF output;
• titles and footnotes;
• standardized validation checks.

Macros and Automated TLF Pipelines

Macros become especially powerful when combined with metadata-driven programming.

Instead of manually writing one program for every table, a control dataset can describe the required outputs.

A driver program can then read the metadata and invoke the appropriate macros.

Macros Do Not Replace Statistical Programming

Automation does not eliminate the need for statistical understanding.

A macro can reliably calculate the instructions it has been given, but it cannot determine whether those instructions represent the correct clinical estimand, population, analysis set, or statistical method.

Production Checklist

Before using a macro-driven clinical table in a production submission workflow, review the following:

• Is the macro specification documented?
• Are all parameters clearly defined?
• Are defaults appropriate and documented?
• Are local macro variables explicitly controlled?
• Are population and denominator rules correct?
• Are treatment groups in the approved order?
• Are formats and decimal places correct?
• Are missing values handled according to the specification?
• Are titles and footnotes correct?
• Has the generated SAS code been reviewed?
• Has the resulting output been independently checked?
• Are temporary datasets isolated from permanent study data?
• Is the macro version controlled?
• Can the same macro be reproduced in a clean environment?

Key Takeaways

Conclusion

SAS macros provide one of the most effective mechanisms for standardizing clinical table generation. The real value is not simply reducing the number of lines of SAS code. The value comes from creating reusable, controlled, and testable reporting components.

A strong macro framework separates the stable mechanics of table generation from the study-specific configuration. Population definitions, treatment groups, analysis variables, titles, footnotes, and display requirements can then be supplied as parameters while the underlying reporting logic remains consistent.

For clinical programmers, the progression from individual table programs to reusable macros is an important step toward scalable TLF automation. The next step is to combine these macros with metadata-driven programming, version control, automated validation, and standardized ADaM-based analysis workflows.

References

SAS Institute Inc. SAS Macro Language: Reference. SAS Institute Inc.
SAS Institute Inc. SAS 9.4 Procedures Guide: PROC REPORT. SAS Institute Inc.
SAS Institute Inc. SAS 9.4 Output Delivery System: User's Guide. SAS Institute Inc.
CDISC. Analysis Data Model (ADaM) Implementation Guide.
FDA. Study Data Technical Conformance Guide.