Automatically Generating Highly Customized Excel ® Reports with SAS/Macro ® and Integration Technologies (DDE)

As a fast growing large public metropolitan research institution, the Office of University Analysis and Planning Support at the University of Central Florida (UCF) is challenged with transforming very large data sets into information that will support strategic planning and decision making. This paper describes a process that utilizes SAS ® macro language and integration technologies with Microsoft Excel ® , to generate a series of highly customized Excel reports in an automated approach. This methodology allows SAS to process a data set of over 400,000 records, consolidate the data, open preformatted Excel templates, and write the data to designated data ranges. The integration technology used, called Dynamic Data Exchange (DDE), reduces report generating time by up to 90%, compared to prior methods that used Excel PivotTables and manual copying and pasting of results, while improving report accuracy by reducing human error. Additionally, SAS macro language enables programmers to customize the report to suit the needs of a variety of end users.