SESUG 2024 Paper 035 - A SAS Automation Example for Completing Reporting Templates in Microsoft Excel - Using a driver table to generate VBA macros

Sep 12, 2026

Monthly reporting often requires completing a provided Microsoft Excel template in a strict layout, which can increase complexity through customization and hardcoding. This example uses a SAS “driver” table to map each worksheet row to the SAS variable supplying the second-column value, and to generate the complete VBA macro text. The exported Excel output is formatted to match the required template, while the driver table can be extended for quality control to reduce human error.

LanguageEnglish

CategoryPresentations

FormatPDF

Pages18

Reading Time6 min

AccessFree to download

ABSTRACT

INTRODUCTION

REPORTING TEMPLATE

“DRIVER” TABLE

USE THE “DRIVER” TABLE TO WRITE THE CODE PROGRAMATICALLY

SESUG 2024 Paper 035 A SAS® Automation Example for Completing Reporting Templates in Microsoft® Excel Imelda C. Go, PhD, Palmetto GBA, Columbia, SC and Abbas S. Tavakoli, DrPH, MPH, ME, University of South Carolina ABSTRACT This example resulted from the need to report monthly data processing results by completing an Excel template used for reporting to the contracting party. In this case, the contracting party provided an Excel file with several formatted worksheets (e.g., first and second columns have bolding, cell shading, etc.) with the expectation that we would fill out the second-column cells . Instead of filling out the Excel template the customer provided, SAS creates an Excel file, which is made to look like the template. This example uses a“driver” table that maps each worksheet row to the SAS variable that has the value for the second column cell. The “driver” table also contains information that allows SAS to write into a text file the complete VBA in Excel macros, which can change the appearance of the exported file to look just like the required template. The “driver” can be extended to include columns that are useful for quality control when the programmer’s work is checked by another programmer . INTRODUCTION Whenever we are required to use a specific format and file to report, the level complexity of the task could increase due to customization, which in turn can lead to quite a bit of hardcoding. Using the “driver” table will help minimize the hardcoding since the table will contain the information needed to direct SAS to write the code we need. Without the driver table, any change in the reporting template could make it more difficult to revise the hardcoding. The value of this technique becomes more evident when the number of rows is large. A programmer might be able to manage a smaller number of rows with hardcoding, but as the number of rows increases, challenges will be greater. Also, last minute changes can leave less time for quality control. The automation safeguards us from human error provided the“driver” table itself is reliable since the SAS coding only does what the “driver” table tells it to. REPORTING TEMPLATE To demonstrate this technique, we use the following contrived example to provide a few examples of the possibilities using a “driver” table, SAS, and VBA in Excel. Let us suppose we are analyzing international wildlife data and are required to report monthly by completing the two Excel Worksheets below currently title for November 2023. The first Excel worksheet named Sightings has 14 rows: The second Excel worksheet named Finances has 9 rows: “DRIVER” TABLE We take the two columns and all rows from the template and include them in the “Driver” Table . We want a generalized format and we note that Cell A1 in both worksheets contain a title with a date that will change each month. We change the value of“November 2023” to ** in the “Driver” table so that we can change ** to the correct month and year using SAS. We also add several columns, which are necessary to direct SAS to write the code for us . The first few additional columns are for SAS to map the SAS variable values to the corresponding row . The rest of the rows are for writing the VBA text that will transform the output into the template . The following table describes the columns in this “Driver” table. The VBA elements are described briefly. How to use VBA in Excel will not be discussed in this paper. | | “Driver” Table Column Name | Description | | --- | --- | --- | | | ColumnA | This is the value of the first column from each worksheet. As noted earlier, we changed “November 2023” to “**” to generalize the information . We will use SAS to replace ** to the correct month and year. | | | ColumnB | This is the contents of the second column from each worksheet. | | | WSIndex | This is the ordinal number for each worksheet as they appear in the reporting template. It is 1 for the Sightings w

FAQs

How are VBA macros generated to make the exported Excel match the template?

The driver table also includes information describing VBA elements such as fonts, font sizes, bolding, and cell shading (RGB). SAS writes the complete VBA text into a text file so the appearance of the exported file matches the template.

Sep 12, 2026

Upload by 8796093062539