July 31 2022 Jake Adler

PROMETRIKA’s statistical programming team is developing methods to enhance the efficiency and maintain high quality of table development while managing changes in the data and the requirements as table programming is in progress.

Background & Challenges: Title and footnote updates in statistical tables for Clinical Study Reports (CSRs) can take a lot of time, especially if there are many programs that need updating.

In general, in the table shells that are provided with the Statistical Analysis Plan (SAP), the title and the footnotes are in the body of the document. The statistical programmers are tasked with copying those titles and footnotes into the headers/footers of the pages of the final tables produced for use in the CSR. It is difficult to programmatically extract titles and footnotes from SAP shells; thus, programmers end up inputting them manually. There has been much trial and error in writing programs to extract the titles and footnotes and place them correctly in the CSR tables. Titles and footnotes are spread over multiple lines; the simplest solution has been to concatenate all those lines with a special character that can break a line when outputting a rich text format (.rtf) table.

Footnote updates can vary in their complexity; sometimes a simple change in capitalization is needed, while other times they need to be entirely rewritten. When creating tables, listings, and figures (TLFs), we want them to be as robust as possible - if the data we import changes, our programs need to account for that. This is hard to do with titles and footnotes since these ‘values’ are just text from a MSWord document. Titles and footnotes are hardcoded in the programs; when we set them to a definite value, they don’t change as data is updated.

Visual Basics for Applications (VBA) is an event-driven programming language from Microsoft that is now primarily used with Microsoft office applications such as Microsoft Excel and Microsoft Word. This application allows macros to be created within Excel and can be used to create a macro to read specific text in a Word document and output into an Excel sheet. From this, we can transfer the information into SAS®.


I have developed a methodology using VBA to automate the process of extracting the titles and footnotes from the PROMETRIKA SAP table shells and importing them directly into SAS. Validation of changes is performed through Apache Subversion (SVN) when data changes occur. This method will be highly beneficial in reducing time spent and errors that may occur with the manual extraction of titles and footnotes.  PROMETRIKA is planning to implement this method in upcoming projects.

“This method will be highly beneficial in reducing time spent and errors that may occur with the manual extraction of titles and footnotes.”

Share This Article