PharmaSUG 2025-Paper - SD-155 - Integrating SAS DDE to Automate Excel Task Tracking in Pharmaceutical Statistical Programming

Sep 12, 2026

Upload by Mason

Pharmaceutical statistical programming teams often rely on multiple interrelated Excel spreadsheets to coordinate deliverables, dataset specifications, and SAS program work. Maintaining consistency across dataset metadata, TLF requirements, and programming/validation status becomes challenging when updates for new studies must preserve formulas and templates. This paper introduces a SAS macro that generates a programming and validation task tracker automatically from existing ADaM dataset specifications and TLF deliverable lists using SAS Dynamic Data Exchange (DDE).

LanguageEnglish

CategoryPresentations

FormatPDF

Pages8

Reading Time3 min

AccessFree to download

ABSTRACT

INTRODUCTION

Planning deliverables with three core documents

KEY DDE SYNTAX

Using SAS to write selected cells to Excel

MACRO DESCRIPTION AND USER INPUTS

tlflist0to0tracking macro parameters

PharmaSUG 2025-Paper SD-155 Integrating SAS DDE to Automate Excel Task Tracking in Pharmaceutical Statistical Programming Amy Zhang and Huei-Ling Chen, Merck & Co. , Inc. , Rahway, NJ, USA ABSTRACT Statistical programming teams in pharmaceutical companies often use Excel spreadsheets to align team members and keep track of the deliverables and programming tasks assigned to programmers and statisticians. Each of these files serves its own purpose, but also shares related information with the others. For instance, the analysis dataset specification file contains spreadsheets with the metadata for all the datasets. Another spreadsheet holds the details for the tables, listings, and figures (TLFs) needed fora deliverable, including TLF titles, output file names, macros, and their calling programs. An additional programming tracking spreadsheet outlines the programming tasks for each SAS program, covering development, validation, and peer review. It is important to ensure that the information in these files remains consistent. Several options exist for creating and populating new spreadsheets, both within SASand externally. However, many of these Excel files have built-in formulas or templates that need to be kept intact when updating them for new studies. This paper presents a SAS macro to generate a programming and validation tasks tracker directly from already available data of ADaM dataset specifications and TLF deliverable list using the SAS Dynamic Data Exchange (DDE) method. INTRODUCTION After a deliverable package has been planned, programmers and statisticians author documents to develop a programming plan. In Merck, we have three such documents: analysis dataset specifications , TLF deliverable list, and programming tracking sheet. The analysis dataset specifications file contains a “Content” tab with high-level overview of all analysis datasets that need to be developed for the deliverable. Each ADaM dataset has its own tab to describe its variables and associated attributes. Figure 1: Content tab of analysis dataset specifications file with the names of all datasets The TLF deliverable list contains the names of all TLFs that need to be generated for the deliverable. Key columns include the TLF title, generated output’s file name, macro name, call program name, and a flag column for the delivery type (for example, CSR), as seen in Figure 2. Figure 2: Snapshot of an example TLF deliverable list A programming tracking sheet contains the names of all SAS programs that need to be created. Key columns include the program name, description of what the program is for, folder in which the program is stored, validation category level, developer’s name and programming status, and validator’s name and validation status, as seen in Figure 3. The first section of the programming tracking sheet shows which analysis datasets are required for this delivery, and this comes from the analysis dataset specifications file. The following section shows which macros are being used to create the TLFs, and this comes from selecting all unique macro call programs from the TLF deliverable list. Therefore, in order to populate the programming tracking sheet and keep its contents consistent with the other files, we need to pull information from both the analysis dataset specifications file and TLF deliverable list. There are also preexisting formulas in certain columns that we want to retain in each copy of the tracking sheet. Figure 3: Snapshot of an example programming tracking sheet with built-in formula Since we are looking for an automated solution to create this tracking spreadsheet while retaining its builtin formulas, we explore SAS Dynamic Data Exchange (DDE), a dynamic method to exchange information between the SAS system and Windows applications. DDE allows us to use standard SAS code to read data from SAS and write it to a Windows application, which is Microsoft Excel in our case. It even has the flexibility t

FAQs

What inputs does the tlflist0to0tracking SAS macro require?

The macro takes eight parameters, including the TLF deliverable list file and tabs, a where clause, the ADaM specifications file, the tracking template path, the output tracking file path, and study path/users information.

Sep 12, 2026

Upload by Mason