A Three-Tier Framework: Global, Standard, and Analysis Macros
Abstract
SAS macros are the engineering backbone of clinical statistical programming. When designed well, they accelerate delivery, enforce consistency across studies, and reduce the risk of errors in regulated submissions. When designed poorly, they become liabilities — hard to validate, impossible to reuse, and costly to maintain.
This article presents a structured, three-tier framework for macro development: Global macros (organization-level utilities such as RTF generation and CTCAE toxicity grading), Standard macros (program-level shared logic such as visit number assignment and unscheduled visit flagging), and Analysis macros (study-specific SAP-driven derivations such as visit windowing for AVISIT/AVISITN). For each tier, the article covers specification design, implementation best practices, and rigorous validation strategies aligned with FDA and ICH-compliant environments.
Clinical statistical programming operates in a uniquely constrained environment. Code must be reproducible, traceable to specifications, validated independently, and defensible to regulators. Unlike general software engineering, there is no room for ambiguity in what a macro is supposed to do, and no tolerance for undocumented behavior.
Macros introduce efficiency but also systemic risk. A subtle defect in a global RTF generation macro, replicated across 50 outputs in a submission package, is not 50 defects — it is a systemic failure. This is why the architecture of a macro library matters as much as the correctness of any individual macro.
The framework described in this article mirrors how mature pharma and biotech programming shops organize their macro infrastructure:
| Tier | Definition and Scope |
| Global | Organization- or department-level utility macros. Study-agnostic. Examples: RTF/PDF output generation, CTCAE toxicity grading, general-purpose statistics computation. |
| Standard | Therapeutic area or program-level macros encoding shared business logic across studies in a development program. Examples: visit number assignment, unscheduled visit flagging. |
| Analysis | Study-specific or analysis-specific macros implementing SAP logic for a given trial. Examples: visit windowing for AVISIT/AVISITN, baseline flag derivation, analysis flag computation. |
One of the most common failures in macro development is beginning to write code before the specification is complete, reviewed, and approved. In a regulated environment, the specification is not documentation that follows the code — it is the contract that precedes and governs the code.
| Element | Requirement |
| Purpose and Scope | One to three sentences. Specific, not generic. Includes CTCAE version, CDISC domain, and SAP section reference where applicable. |
| Inputs | Every parameter: name, type, default, required/optional. For dataset parameters: expected variables, roles, and sort order. |
| Outputs | Every output dataset, variable, format, and macro variable. Include variable names, types, lengths, labels, and format. |
| Processing Logic | Step-by-step algorithm in plain language. Precise enough that two independent programmers produce identical results. |
| Assumptions | What the macro assumes about inputs. What happens when assumptions are violated. |
| Error Handling | Behavior for: missing required parameters, empty datasets, unexpected missing values, unexpected variable absence. |
| Version History | Version number, date, author, and change description for every revision. |
| References | CTCAE version, CDISC IG section, ADaM IG, SAP section, or any governing document. |
| Key Principle | Specificity in scope statements is not optional. "Derives LBTOXGR using CTCAE" is inadequate. "Assigns NCI CTCAE v5.0 toxicity grade to laboratory findings in ADLB based on absolute value thresholds from the CTCAE criteria reference dataset, supporting sex-specific normal ranges where applicable" is a proper scope statement. |
Before any code is written, the specification should undergo a structured review. For global macros, review should include the lead programmer, a senior programming representative, and the biostatistician who can validate the algorithmic logic. For analysis macros, the SAP author should explicitly confirm the specification faithfully implements the SAP intent.
Once a specification is approved, changes follow formal change control. Any modification to behavior requires a specification revision, code update, and re-validation proportionate to scope. A behavioral change to a global macro without proper change control can invalidate outputs across multiple studies simultaneously.
Global macros are maintained by a designated owner — typically a programming lead or standards team — versioned in a controlled repository, and promoted to production only after full validation. Studies consume them as a dependency; they do not modify them.
An RTF generation macro sits beneath every table, listing, and figure produced in a study. Its correctness is a prerequisite for the validity of every output in a submission.
Key Parameters
| Parameter | Description |
| data= | Required. Input dataset name. |
| file= | Required. Full output file path without extension. |
| orient= | Optional. portrait|landscape. Default: portrait. |
| title1–5= | Optional. Title lines 1 through 5. |
| foot1–3= | Optional. Footnote lines 1 through 3. |
| colwidths= | Required. Space-delimited column widths in inches. |
| fontsize= | Optional. Font size in points. Default: 9. |
| destination= | Optional. RTF|PDF|BOTH. Default: RTF. |
Implementation Pattern
| %macro rtf_output( |
| data = , /* Required: Input dataset */ |
| file = , /* Required: Output path without extension */ |
| orient = portrait, /* Optional: portrait|landscape. Default: portrait */ |
| title1 = , /* Optional: First title line */ |
| title2 = , /* Optional: Second title line */ |
| title3 = , /* Optional: Population line */ |
| foot1 = , /* Optional: First footnote line */ |
| foot2 = , /* Optional: Second footnote line */ |
| colwidths = , /* Required: Space-delimited widths (inches) */ |
| fontsize = 9 /* Optional: Font size in points. Default: 9 */ |
| ); |
| /*** Parameter validation ***/. |
| %if %sysevalf(%superq(data)=, boolean) %then %do; |
| %put ERROR: [rtf_output] Required parameter DATA= not specified.; |
| %return; |
| %end; |
| %if not %sysfunc(exist(&data.)) %then %do; |
| %put ERROR: [rtf_output] Dataset &data. does not exist.; |
| %return; |
| %end; |
| %local lmargin rmargin pagewidth; |
| %if %upcase(&orient.) = LANDSCAPE %then %do; |
| %let lmargin = 0.5in; %let pagewidth = 11in; |
| %end; |
| %else %do; |
| %let lmargin = 1.0in; %let pagewidth = 8.5in; |
| %end; |
| options nodate nonumber orientation=&orient. |
| leftmargin=&lmargin. rightmargin=&lmargin.; |
| ods escapechar = "^"; |
| ods rtf file="&file..rtf" style=journal startpage=never; |
| title1 j=l "^S={font_size=&fontsize.pt} &title1."; |
| title3 j=l "^S={font_size=&fontsize.pt} &title3."; |
| footnote1 j=l "^S={font_size=&fontsize.pt} &foot1."; |
| /* Caller drives PROC REPORT with colwidths= */ |
| ods rtf close; |
| title; footnote; |
| %mend rtf_output; |
The derivation of LBTOXGR — the NCI CTCAE toxicity grade for laboratory findings — is one of the most clinically consequential derivations in oncology programming. Errors here can affect safety narratives, dose modification analyses, and regulatory tables.
The CTCAE Grading Algorithm
CTCAE grading for labs uses thresholds expressed as multiples of the ULN or LLN. For a given lab test, grade boundaries are:
•Grade 0: Within normal limits
•Grade 1: Mild abnormality (e.g., > ULN to 3× ULN for ALT)
•Grade 2: Moderate abnormality
•Grade 3: Severe abnormality
•Grade 4: Life-threatening consequence
•Grade 5: Death (rarely assigned for laboratory values)
| Critical Design Decision | The CTCAE threshold reference dataset must be maintained externally — never embedded in the macro. This allows threshold updates as CTCAE versions evolve (v4.0 to v5.0, and beyond) without touching macro code. The macro specification must document the CTCAE version and reference dataset version it was validated against. |
Macro Architecture
| %macro ctcae_grade( |
| indata = , /* Required: Input lab dataset */ |
| outdata = , /* Required: Output dataset name */ |
| ctcaeref = , /* Required: CTCAE threshold reference dataset */ |
| lbtestcd = LBTESTCD, /* Lab test code variable */ |
| lbstresn = LBSTRESN, /* Numeric result variable */ |
| lbstnrhi = LBSTNRHI, /* ULN variable */ |
| lbstnrlo = LBSTNRLO, /* LLN variable */ |
| sex = SEX, /* Sex variable for sex-specific thresholds */ |
| gradevar = LBTOXGR /* Output grade variable name */ |
| ); |
| %if not %sysfunc(exist(&indata.)) %then %do; |
| %put ERROR: [ctcae_grade] Input dataset &indata. not found.; %return; |
| %end; |
| %if not %sysfunc(exist(&ctcaeref.)) %then %do; |
| %put ERROR: [ctcae_grade] CTCAE reference &ctcaeref. not found.; %return; |
| %end; |
| proc sort data=&indata. out=_cg_work; by &lbtestcd. &sex.; run; |
| proc sort data=&ctcaeref. out=_cg_ref; by lbtestcd sex; run; |
| data &outdata.; |
| merge _cg_work (in=a) |
| _cg_ref (in=b rename=(lbtestcd=&lbtestcd. sex=&sex.)); |
| by &lbtestcd. &sex.; |
| if a; /* Retain all lab records; CTCAE ref may not cover every test */ |
| length &gradevar. 8; |
| label &gradevar. = "NCI CTCAE Toxicity Grade"; |
| /* Grade assignment: high (above ULN) */ |
| if not missing(&lbstresn.) and not missing(&lbstnrhi.) |
| and &lbstnrhi. > 0 and not missing(g1_hi_mult) then do; |
| if &lbstresn. >= g4_hi_mult * &lbstnrhi. then &gradevar. = 4; |
| else if &lbstresn. >= g3_hi_mult * &lbstnrhi. then &gradevar. = 3; |
| else if &lbstresn. >= g2_hi_mult * &lbstnrhi. then &gradevar. = 2; |
| else if &lbstresn. >= g1_hi_mult * &lbstnrhi. then &gradevar. = 1; |
| else &gradevar. = 0; |
| end; |
| /* Grade assignment: low (below LLN) */ |
| else if not missing(&lbstresn.) and not missing(&lbstnrlo.) |
| and &lbstnrlo. > 0 and not missing(g1_lo_mult) then do; |
| if &lbstresn. <= g4_lo_mult * &lbstnrlo. then &gradevar. = 4; |
| else if &lbstresn. <= g3_lo_mult * &lbstnrlo. then &gradevar. = 3; |
| else if &lbstresn. <= g2_lo_mult * &lbstnrlo. then &gradevar. = 2; |
| else if &lbstresn. <= g1_lo_mult * &lbstnrlo. then &gradevar. = 1; |
| else &gradevar. = 0; |
| end; |
| else &gradevar. = .; /* Missing data or test not in CTCAE reference */ |
| drop g1_hi_mult g2_hi_mult g3_hi_mult g4_hi_mult |
| g1_lo_mult g2_lo_mult g3_lo_mult g4_lo_mult; |
| run; |
| /* Audit: warn on records where grade could not be assigned */ |
| proc sql noprint; |
| select count(*) into :_nungr trimmed |
| from &outdata. |
| where missing(&gradevar.) and not missing(&lbstresn.); |
| quit; |
| %if &_nungr. > 0 %then |
| %put WARNING: [ctcae_grade] &_nungr. records with non-missing result |
| could not be graded. Verify CTCAE reference coverage.; |
| proc datasets lib=work nolist; delete _cg_work _cg_ref; run; quit; |
| %mend ctcae_grade; |
Statistical summary macros — computing means, standard deviations, medians, confidence intervals — are ubiquitous in clinical programming. A well-designed statistics macro separates computation from presentation, allowing the same core computation to feed different output formats.
| Parameter | Description |
| indata= | Input dataset. |
| outdata= | Output dataset in long format (STATISTIC, VALUE columns). |
| classvar= | Space-delimited classification variables for by-group processing. |
| anavar= | Space-delimited analysis variable list. |
| stats= | Statistics to compute: N MEAN SD MEDIAN MIN MAX Q1 Q3 NMISS. |
| missing= | Y = include missing as a classification level. |
| Design Principle | Never hard-code statistic formatting in a computation macro. Produce raw numeric values in a long-format output dataset. Downstream presentation macros apply formats, decimal places, and display rounding. This separation makes the computation layer independently verifiable and reusable across output formats. |
Standard macros encode logic shared across multiple studies within a program or therapeutic area, but not generic enough for the global library. They are owned by the program-level programming lead and versioned at the program level. Studies consume them as-is; customization occurs through parameters, not modification.
In Phase 2 and 3 programs with a common protocol structure, the definition of an unscheduled visit is standardized in the protocol. A standard macro applies this consistent definition and produces a flag variable used downstream by analysis macros.
| %macro flag_unscheduled( |
| indata = , |
| outdata = , |
| visitvar = VISIT, |
| unsched_pat = UNSCHEDULED|UNSCHED|EXTRA|EARLY TERM, |
| flagvar = UNSCHED |
| ); |
| data &outdata.; |
| set &indata.; |
| length &flagvar. $1; |
| label &flagvar. = "Unscheduled Visit Flag"; |
| &flagvar. = "N"; |
| %local i pat; |
| %let i = 1; |
| %do %while(%scan(&unsched_pat., &i., "|") ne ); |
| %let pat = %scan(&unsched_pat., &i., "|"); |
| if index(upcase(&visitvar.), "%upcase(&pat.)") > 0 then &flagvar. = "Y"; |
| %let i = %eval(&i. + 1); |
| %end; |
| run; |
| %mend flag_unscheduled; |
A companion macro assigns VISITNUM from a study-level or program-level visit schedule reference dataset. This is critical for SDTM TV alignment and downstream ADaM visit windowing.
| %macro assign_visitnum( |
| indata = , |
| outdata = , |
| visitref = , /* VISIT, VISITNUM reference dataset */ |
| visitvar = VISIT, |
| vistnvar = VISITNUM |
| ); |
| proc sort data=&indata. out=_av_work; by &visitvar.; run; |
| proc sort data=&visitref. out=_av_ref; by &visitvar.; run; |
| data &outdata.; |
| merge _av_work (in=a) |
| _av_ref (in=b keep=&visitvar. &vistnvar.); |
| by &visitvar.; |
| if a; |
| if not b and not missing(&visitvar.) then |
| put "WARNING: [assign_visitnum] VISIT=" &visitvar. |
| "not found in reference for USUBJID=" USUBJID; |
| run; |
| proc datasets lib=work nolist; delete _av_work _av_ref; run; quit; |
| %mend assign_visitnum; |
Analysis macros implement the specific derivations described in the SAP. Every parameter, every algorithmic choice, and every output variable must trace directly to the SAP. They are the most tightly regulated tier.
Visit windowing assigns analysis visit (AVISIT, AVISITN) to actual observations based on a configurable window around the planned visit day. This is among the most complex and error-prone derivations in ADaM ADLB — and one of the most consequential, as errors directly affect primary and key secondary endpoint analyses.
Windowing Specification: Required Decisions
•Window reference dataset structure (AVISITN, AVISIT, planned day, WINLO, WINHI)
•Tie-breaking rule: when multiple records fall in the same window — typically the record closest to the planned visit day; if still tied, the earlier date
•Handling of records in no window: AVISIT = "Unscheduled", AVISITN = 9999
•Handling of overlapping windows: rare, but must be explicitly specified
•Whether baseline is excluded from windowing (typically yes — handled separately)
Implementation
| %macro window_visits( |
| indata = , |
| outdata = , |
| winref = , /* AVISITN, AVISIT, WINLO, WINHI, AVISDY */ |
| adyvar = ADY, |
| unsched = Y /* Y = retain unwindowed records */ |
| ); |
| /* Step 1: Cross-join to window reference; retain records in window */ |
| proc sql; |
| create table _wv_join as |
| select a.*, |
| b.avisitn, b.avisit, b.avisdy, b.winlo, b.winhi, |
| abs(a.&adyvar. - b.avisdy) as _dist |
| from &indata. a |
| left join &winref. b |
| on a.&adyvar. between b.winlo and b.winhi |
| order by a.usubjid, a.&adyvar., _dist; |
| quit; |
| /* Step 2: Select closest-to-planned record per observation */ |
| data _wv_best; |
| set _wv_join; |
| by usubjid &adyvar. _dist; |
| if first.&adyvar.; |
| run; |
| /* Step 3: Handle records with no window match */ |
| data &outdata.; |
| set _wv_best; |
| %if %upcase(&unsched.) = Y %then %do; |
| if missing(avisitn) then do; |
| avisit = "Unscheduled"; |
| avisitn = 9999; |
| end; |
| %end; |
| %else %do; |
| if missing(avisitn) then delete; |
| %end; |
| drop winlo winhi avisdy _dist; |
| run; |
| proc datasets lib=work nolist; delete _wv_join _wv_best; run; quit; |
| %mend window_visits; |
In oncology studies where LBTOXGR is a key safety endpoint, the handling of unscheduled lab assessments within the windowed ADLB framework requires precise specification. The SAP will specify one of three common strategies:
| Strategy | Description and Use Case |
| Exclude from windowed analysis | Unscheduled labs appear in data listings with AVISIT = "Unscheduled" and AVISITN = 9999. They do not contribute to any scheduled analysis visit. Appropriate when the protocol does not specify clinical action based on unscheduled labs. |
| Include in nearest window | Unscheduled labs compete with scheduled records within the same window. Tie-breaking rules determine which record is selected. Appropriate when unscheduled labs may represent the only measurement in a window. |
| Worst-grade analysis only | Unscheduled labs do not compete for the primary windowed record but are included in maximum post-baseline toxicity grade analyses regardless of window assignment. Most common in oncology safety analyses. |
Implementation: Strategy 3 — Maximum Post-Baseline Grade
| /* Maximum post-baseline LBTOXGR across all records (including unscheduled) */ |
| proc sort data=adlb_windowed out=_lb_sort; |
| by usubjid lbtestcd; |
| run; |
| data adlb_maxgr; |
| set _lb_sort; |
| by usubjid lbtestcd; |
| retain _maxgr .; |
| if first.lbtestcd then _maxgr = .; |
| /* All post-baseline records regardless of UNSCHED flag */ |
| if ablfl ne "Y" and not missing(lbtoxgr) then |
| _maxgr = max(_maxgr, lbtoxgr); |
| if last.lbtestcd then do; |
| maxlbtoxgr = _maxgr; |
| output; |
| end; |
| run; |
Every macro must begin with a standardized header block. This block serves as the primary documentation artifact for both programmers and validators.
| /****************************************************************************** |
| ** Macro Name : window_visits |
| ** Version : 1.2 |
| ** Author : V. Debbeti |
| ** Created : 2024-01-15 |
| ** Last Modified: 2024-08-20 v1.2 Added overlapping window detection |
| ** |
| ** Purpose : Assigns AVISIT and AVISITN to ADaM records using study- |
| ** specific visit windows as defined in SAP Section 9.3. |
| ** |
| ** Parameters : |
| ** indata - (Required) Input dataset name |
| ** outdata - (Required) Output dataset name |
| ** winref - (Required) Visit window reference dataset |
| ** adyvar - (Optional) Study day variable. Default: ADY |
| ** unsched - (Optional) Retain unwindowed records Y/N. Default: Y |
| ** |
| ** Outputs : |
| ** &outdata. - AVISIT, AVISITN assigned; UNSCHED flag included |
| ** |
| ** Dependencies : |
| ** Macros: none |
| ** Reference datasets: &winref. (AVISITN, AVISIT, WINLO, WINHI, AVISDY) |
| ** |
| ** Specification: ADaM_ADLB_ProgSpec_v2.1.docx, Section 4.2 |
| *****************************************************************************/ |
All macro variables created within a macro for internal use must be declared %LOCAL at the top of the macro. Failure to do so allows internal variables to leak into the calling environment — potentially overwriting values the caller depends on. This is one of the most common and hardest-to-trace bugs in SAS macro code.
| %macro window_visits(indata=, outdata=, winref=, adyvar=ADY, unsched=Y); |
| %local i n_in n_out n_unsched dsid rc; /* ALL internal vars declared LOCAL */ |
| /* ... processing ... */ |
| %mend window_visits; |
Macros must validate inputs before performing any work. When validation fails, emit a clear error message prefixed with the macro name, and return without generating output. Never silently produce wrong output.
| /* Reusable variable-existence check utility */ |
| %macro check_var(dsn=, var=); |
| %local dsid rc; |
| %let dsid = %sysfunc(open(&dsn.)); |
| %if &dsid. = 0 %then %do; |
| %put ERROR: [check_var] Cannot open dataset &dsn.; |
| %return; |
| %end; |
| %if %sysfunc(varnum(&dsid., &var.)) = 0 %then %do; |
| %put ERROR: [check_var] Variable &var. not found in &dsn.; |
| %let rc = %sysfunc(close(&dsid.)); |
| %return; |
| %end; |
| %let rc = %sysfunc(close(&dsid.)); |
| %mend check_var; |
Macros that create intermediate work datasets must use names prefixed with underscore and the macro abbreviation (e.g., _wv_join, _wv_best for window_visits). Delete all intermediate datasets at macro exit using proc datasets. Leaving work datasets in the library creates confusion and may mask validation errors.
Macros should write informative messages to the SAS log throughout execution. At minimum: macro name and version at entry; key parameter values as resolved; record counts in/out; data quality warnings; and completion confirmation.
| %put NOTE: [window_visits v1.2] Starting execution.; |
| %put NOTE: [window_visits] Parameters: indata=&indata., outdata=&outdata., |
| winref=&winref., unsched=&unsched.; |
| /* ... processing ... */ |
| %put NOTE: [window_visits] Input: &_n_in. records. Output: &_n_out. records. |
| Unscheduled retained: &_n_unsched.; |
| %put NOTE: [window_visits v1.2] Execution complete.; |
Validation of SAS macros in clinical programming is not optional and is not adequately served by a programmer reviewing their own code. Regulatory expectations (ICH E6, GAMP 5) imply that software used in clinical data processing must be validated. Industry practice requires independent validation — a second programmer independently develops and executes verification tests against the specification, not against the original code.
| Tier | Required Validation Approach |
| Global | Full independent parallel testing. Validator independently implements the algorithm and compares outputs record-by-record. Formal Validation Report retained in quality system. |
| Standard | Independent testing at initial implementation and upon modification. Test case library scoped to program-specific scenarios. Documented test plan and results retained. |
| Analysis | Independent programming from SAP specification. QC programmer derives expected output independently and compares via proc compare. Embedded in study-level QC documentation. |
| Category | Description and Examples |
| Positive | Inputs satisfying expected conditions. Cover primary use case, full parameter range, and all distinct algorithmic paths (each CTCAE grade boundary, each windowing scenario). |
| Boundary | Inputs at exact boundary values. For CTCAE: values exactly at Grade 1/2, 2/3, 3/4 thresholds. For windowing: observation exactly at WINLO and exactly at WINHI. |
| Negative | Inputs violating assumptions: missing required parameters, empty input datasets, missing expected variables, values outside expected ranges. Expected behavior must be pre-specified. |
| Edge | Unusual but valid: subject with no post-baseline observations, lab test absent from CTCAE reference, visit day in no window, all-missing results, single-observation dataset. |
| proc compare base = expected_output |
| compare = macro_output |
| out = _comp_results |
| outnoequal |
| method = absolute |
| criterion = 1e-8 /* Tight tolerance for numeric comparisons */ |
| listall; |
| run; |
| /* Accumulate pass/fail across all test cases */ |
| data validation_summary; |
| set _comp_results end=eof; |
| retain n_diff 0; |
| n_diff + 1; |
| if eof then do; |
| if n_diff = 0 then result = "PASS"; |
| else result = "FAIL: " || strip(put(n_diff, 8.)) || " discrepancies"; |
| output; |
| end; |
| run; |
•Specification version being validated against
•Validator's name and date
•Test cases executed: input data description and expected output for each
•Test results: explicit pass/fail for every test case
•For any failures: root cause analysis and resolution
•Final validation conclusion with validator signature/approval
When any macro is modified, the full test case library must be re-executed. For global macros with extensive test libraries, this should be automated: a test driver program that runs all test cases sequentially and produces a consolidated pass/fail summary. The version of a macro used to produce any given output must be traceable. No macro may be modified in production without a version increment and re-validation.
| Version Control Minimum Requirements | Acceptable systems include Git (increasingly common in pharma environments) or a controlled file structure with version-encoded naming conventions. Requirements: (1) version used for any output is traceable, (2) no in-place modification without version increment, (3) rollback to prior version is possible. |
A complete ADaM ADLB dataset for an oncology study with lab toxicity grading illustrates how all three tiers interact in sequence. Each macro has its own specification, validation, and version. The ADLB programmer spec references each macro by name and version.
| Step | Macro and Tier | Purpose |
| 1 | flag_unscheduled (Standard) | Flag unscheduled visits from VISIT value patterns per protocol |
| 2 | assign_visitnum (Standard) | Assign VISITNUM from program visit schedule reference |
| 3 | ctcae_grade (Global) | Assign LBTOXGR using CTCAE v5.0 reference dataset |
| 4 | window_visits (Analysis) | Assign AVISIT/AVISITN per SAP visit window definitions |
| 5 | derive_baseline (Analysis) | Assign ABLFL per study-specific baseline definition in SAP |
| 6 | derive_maxgr (Analysis) | Compute maximum post-baseline grade per SAP analysis flags |
| 7 | rtf_output (Global) | Generate lab toxicity summary table for submission package |
| Defect | Prevention |
| Hard-coded values (visit windows, CTCAE thresholds, format strings) in macro code | All configurable values belong in reference datasets or parameters. Hard-coded values cannot be maintained without code changes, which require re-validation. |
| Unchecked sort assumptions | Macros should sort input data themselves (documented in spec) or assert sort order and fail loudly when not met. Never assume sort order silently. |
| Silent missing propagation through arithmetic | Every derivation using missing-sensitive operations must explicitly test for missing inputs. A missing LBSTNRHI in CTCAE grading produces missing grade without error unless explicitly handled. |
| Macro variable scope leakage (%LOCAL omission) | Declare ALL internal macro variables %LOCAL at top of macro. Leakage corrupts the calling environment in context-dependent ways that are extremely difficult to debug. |
| Specification-code divergence | Version numbers on both spec and code. Explicit review step confirming alignment before validation begins. |
| Insufficient boundary test coverage | Values at exact grade or window boundaries are mandatory test cases. Off-by-one errors in comparisons (> vs >=) are among the most common CTCAE and windowing defects. |
The three-tier macro framework is not merely an organizational convenience — it is a risk management structure. Global macros carry the highest reuse risk and demand the most rigorous specification, implementation discipline, and validation intensity. Analysis macros are closest to the clinical question and demand the tightest traceability to the SAP. Standard macros bridge the two and enable program-level consistency.
The investment in specification quality before coding pays dividends at every subsequent stage: cleaner code, faster validation, and a defensible audit trail for regulatory review. The investment in test case design — particularly boundary and negative cases — catches defects before they propagate into submission outputs. And the investment in version control and promotion processes protects the integrity of the macro infrastructure over the full lifecycle of a development program.
Statistical programmers who master this framework are not merely writing better SAS code. They are building the engineering infrastructure that makes reliable, reproducible, regulatory-grade clinical data possible.
The companion specification workbooks for this article — provided as downloadable Excel files — demonstrate the recommended structure for each macro tier. The following tables summarize the key content of each file.
This workbook covers %rtf_output (v2.3), %ctcae_grade (v1.4), and %gen_stats (v1.1). It is structured across four sheets: Cover (document metadata and change log), %rtf_output (full parameter specification and error handling table), %ctcae_grade (parameters, CTCAE reference dataset structure, example reference rows), and Processing Logic (step-by-step algorithm for LBTOXGR derivation).
Figure 11.1 — GLOBAL_MacroSpec.xlsx Sheet Structure
| Sheet | Primary Content | Key Tables | Reviewer Notes |
| Cover | Document metadata and change log | Meta block, change history table | Document ID, Version, Status, CTCAE version, SAP reference, reviewer/approver sign-off fields. Change log tracks every version revision. |
| %rtf_output | Full parameter specification and error handling | Parameter table (15 rows), Error handling table (6 conditions) | Each parameter row: type, required/optional, default, allowed values, description. Error table includes log message text and severity. |
| %ctcae_grade | CTCAE grade derivation parameters and reference structure | Parameter table (9 rows), Reference dataset structure (10 variables), Example reference rows | Reference dataset structure includes all threshold multiplier variables with allowed values and sex-specific notes. |
| Processing Logic | Step-by-step LBTOXGR derivation algorithm | 8-step algorithm table with SAS implementation notes | Each step: phase (validation/sort/merge/grade/audit/cleanup), logic description, SAS implementation notes, output or next step. |
This workbook covers %flag_unscheduled (v1.2), %assign_visitnum (v1.1), %window_visits (v1.2), %derive_baseline (v1.0), and %derive_maxgr (v1.0) for Program ONCX. It is structured with a Cover sheet documenting global dependencies (the global spec GLB-SPEC-2024-001), and dedicated sheets for each macro. The %window_visits sheet includes the full ONCX-301 visit window table with WINLO, WINHI, AVISDY, and tie-breaking rules directly traceable to SAP Section 9.3.
Figure 11.2 — Study ONCX-301 Visit Window Reference (from %window_visits Spec Sheet)
| AVISITN | AVISIT | WINLO | WINHI | AVISDY (Planned) | Window Width |
| 2 | WEEK 2 | 8 | 21 | 14 | 14 days |
| 4 | WEEK 4 | 22 | 35 | 28 | 14 days |
| 8 | WEEK 8 | 36 | 70 | 56 | 35 days |
| 12 | WEEK 12 | 71 | 98 | 84 | 28 days |
| 16 | WEEK 16 | 99 | 126 | 112 | 28 days |
| 24 | WEEK 24 | 127 | 182 | 168 | 56 days |
| 36 | WEEK 36 | 183 | 252 | 252 | 70 days |
| 48 | WEEK 48 | 253 | 350 | 336 | 98 days |
| 9999 | UNSCHEDULED | — | — | — | Assigned by macro when record outside all windows (UNSCHED=Y) |
Source: ONCX-301 SAP Section 9.3, Table 9.3.1. Tie-breaking: closest to AVISDY; on equal distance, earlier ADY selected. All windows non-overlapping.
The specification sheet structure is similar across tiers, but the content and ownership model differ in meaningful ways that reflect the risk profile of each tier.
| Spec Element | Global Macro | Standard Macro | Analysis Macro |
| Owner | Programming Standards Team | Program Programming Lead | Study Programmer (with SAP author review) |
| SAP Traceability | Not required (study-agnostic) | Not required (program-level) | Mandatory — every parameter traces to SAP section |
| CTCAE/Reference Version | Specified in Cover sheet meta | Specified if applicable | Confirmed and locked at study start |
| Algorithm Detail | Full step-by-step table required | Step-by-step if complex | Full step-by-step; decision tree if windowing or grading |
| Error Handling Table | Mandatory (all conditions) | Mandatory (all conditions) | Mandatory (including empty dataset handling) |
| Visit Window Table | Not applicable | Not applicable | Mandatory — full WINLO/WINHI/AVISDY/width table |
| Change Control | Formal (impacts all studies) | Program-level sign-off | Study-level sign-off (SAP amendment if algorithm changes) |
| Reviewer Sign-off | Prog. Lead + Biostatistician + QA | Prog. Lead + Biostatistician | SAP Author confirmation + Prog. Lead |
The companion validation workbook (Macro_Validation_Report.xlsx) demonstrates the recommended structure for macro validation documentation. It contains four sheets: a high-level Validation Summary across all 24 test cases, a detailed %ctcae_grade test sheet with grade boundary verification, a detailed %window_visits test sheet with tie-breaking scenarios, and a PROC COMPARE log with validator sign-off.
The summary sheet provides a consolidated view of all test cases across all macros being validated. Four KPI cells at the top — Total Test Cases, PASS count, FAIL count, and WARN (needs review) count — give the reviewer an instant status read. Each row in the test table contains the test case ID, macro name, category (Positive/Boundary/Negative/Edge/Tie-break), test input summary, expected result, actual result, and status.
Figure 12.1 — Test Case Distribution by Category and Macro
| Macro | Positive | Boundary | Negative | Edge | Tie-break | Total |
| %rtf_output | 2 | 0 | 3 | 1 | 0 | 6 |
| %ctcae_grade | 4 | 4 | 1 | 2 | 0 | 11 |
| %window_visits | 2 | 2 | 1 | 3 | 2 | 10 |
| TOTAL | 8 | 6 | 5 | 6 | 2 | 27 |
The %ctcae_grade detailed test sheet contains two sections: ALT (high-direction grading, all four grade boundaries) and HGB (low-direction sex-specific grading). The ALT section is particularly important — it verifies that the >= comparison operator is used at every grade boundary, and that Grade 4 is evaluated first (from highest to lowest) to prevent incorrect grade assignment.
Figure 12.2 — ALT Grade Boundary Test Cases (extract from Macro_Validation_Report.xlsx)
| TC ID | Type | LBTESTCD | LBSTRESN | LBSTNRHI (ULN) | Ratio to ULN | Expected Grade | Status |
| CTCAE-BND-000 | Boundary | ALT | 34.9 | 35 | 0.997× | 0 (Grade 0) | PASS |
| CTCAE-BND-001 | Boundary | ALT | 35.0 | 35 | 1.000× | 1 (Grade 1) | PASS |
| CTCAE-BND-002 | Boundary | ALT | 105.0 | 35 | 3.000× | 2 (Grade 2) | PASS |
| CTCAE-BND-003 | Boundary | ALT | 175.0 | 35 | 5.000× | 3 (Grade 3) | PASS |
| CTCAE-BND-004 | Boundary | ALT | 700.0 | 35 | 20.000× | 4 (Grade 4) | PASS |
| CTCAE-NEG-001 | Negative | ALT | .(missing) | 35 | N/A | .(missing) | PASS |
ALT CTCAE v5.0 thresholds: Grade 1 ≥ 1.0×ULN, Grade 2 ≥ 3.0×ULN, Grade 3 ≥ 5.0×ULN, Grade 4 ≥ 20.0×ULN. The boundary at exactly 1.000×ULN (TC CTCAE-BND-001) confirms the >= comparison is in use, not >. A value of 0.997×ULN (TC CTCAE-BND-000) correctly produces Grade 0.
The %window_visits test sheet includes two tie-breaking test cases that are among the most critical validation scenarios. A tie in distance from the planned visit day is a realistic scenario — subjects often have labs drawn symmetrically around the planned visit. The tie-breaking rule (earlier ADY wins on equal distance) must be explicitly tested, not assumed.
Figure 12.3 — Visit Windowing Tie-Breaking Test Cases
| TC ID | Type | ADY(1) | ADY(2) | AVISDY | |Dist| (1),(2) | Tie-Break Rule Applied | Status |
| WV-TIE-001 | Tie-break | 26 | 30 | 28 | 2, 2 | Equal distance → earlier ADY wins → ADY=26 selected | PASS |
| WV-TIE-002 | Tie-break | 25 | 31 | 28 | 3, 3 | Equal distance → earlier ADY wins → ADY=25 selected | PASS |
The PROC COMPARE log sheet in the validation workbook documents the dataset-level comparison for every test case. Each row shows the base (expected) and compare (macro output) dataset names, observation counts in each, and the comparison result. A zero-discrepancy result must be confirmed for every positive, boundary, and tie-breaking case. The observation count check (base vs compare) is equally important — a match with different observation counts indicates records are silently being lost or gained.
| Validation Pitfall | PROC COMPARE reporting zero differences does not confirm the observation count is correct. Always verify N(base) = N(compare) explicitly, or use a PROC SQL count comparison alongside PROC COMPARE. A macro that silently deletes records will show zero differences among the records it does produce. |
Every macro moves through a defined lifecycle from initial development to production use and eventual retirement. Understanding and enforcing this lifecycle is what separates a controlled macro library from an ad hoc collection of code files.
Figure 13.1 — Macro Development and Promotion Lifecycle
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Specification Draft | Specification Review | Spec Approved | Macro Coded | Independent Validation | Validation Approved | Production Release |
| Programmer drafts spec per template. Scope, params, algorithm, error handling, version history. | Peer review + biostatistician review. Comment resolution documented. SAP author confirmation for analysis macros. | Formal approval recorded. Version number assigned. Change control process activated. | Programmer implements spec. Header block, %LOCAL, validation, logging, cleanup all applied. | Validator independently tests against spec (not code). All positive/boundary/negative/edge cases executed. | Zero FAIL results. WARNs resolved or documented. Validation report signed off. | Macro deployed to production library. Version communicated to all users. Superseded version archived. |
For global macros, Steps 1–7 require formal documentation retained in the quality system. For analysis macros, Steps 3 and 6 may be documented within the study-level QC documentation package.
The required validation intensity scales with the reuse scope and risk level of each macro. The following table summarizes minimum validation requirements across the three tiers. Organizations may exceed these requirements; they should not fall below them.
Figure 13.2 — Minimum Validation Requirements by Tier
| Validation Requirement | Global Macro | Standard Macro | Analysis Macro |
| Independent testing required | Yes — mandatory | Yes — mandatory | Yes — via independent programming |
| Validator separate from developer | Yes — different person | Yes — different person | Yes — QC programmer |
| Positive test cases | Minimum 2 per major path | Minimum 2 per major path | Minimum 1 per analysis visit/comparison |
| Boundary test cases | All grade/window boundaries | Key boundaries | All window and flag boundaries |
| Negative test cases | All specified error conditions | All specified error conditions | Key error conditions |
| Edge test cases | Empty dataset, missing vars, zero ULN | Empty dataset, missing vars | Empty dataset, out-of-window |
| Tie-breaking test cases | N/A for most global macros | N/A | Mandatory for windowing macros |
| PROC COMPARE documentation | Formal (retained in QS) | Formal (retained in QS) | Study QC documentation |
| Regression test suite required | Yes — automated | Yes — manual acceptable | Per-study re-validation on SAP change |
| Formal validation report | Yes — signed off | Yes — signed off | Embedded in QC documentation |
No comments yet. Be the first!