Building a System of Excel Macros Executable by SAS - Exchanging Data Between SAS and Microsoft Excel - page 189

Chapter 12: Building a System of Excel Macros
Executable by SAS
12.1 Introduction............................................................................................... 189
12.2 Purpose ..................................................................................................... 190
12.3 General Design of a Tool to Control Excel Macros from SAS..................... 190
12.3.1 Prepare a SAS File and Execute Excel to Process the Output ..........................191
12.3.2 Prepare Excel to Open the File Output by SAS....................................................192
12.3.3 Prepare Excel Macros to Reformat the Excel Workbooks.................................194
12.4 Automate the Tool So That SAS Creates a Formatted Excel Output
Workbook .......................................................................................................... 197
12.4.1 Eliminate the Manual Steps from the Processing ...............................................197
12.4.2 Create a SAS Output File with More Data and Control Information..................202
12.4.3 Create an Excel Macro to Process the Output SAS File.....................................203
12.4.4 Build an Excel Graph Using an Excel Macro........................................................207
12.5 Conclusion ................................................................................................ 209
12.1 Introduction
Chapters 12, 13, and 14 each define separate, but increasingly powerful tools that combine SAS and Excel
features and that integrate your ability to transfer data between SAS and Excel. At the beginning of each of
these chapters I have placed a short list of all of the tools--just in case you look at only one of the chapters
today.
This chapter presents SAS code and Excel code that creates a fully integrated system to generate macro-
free Excel workbooks, without downloading any external data or code from the Internet, using SAS 8 and
Excel 97 and later. Software advances today have moved more and more to what I like to call “Push-
Button” processes. I prefer to write software that allows me to turn over the processing to someone who
only needs to know how to push the button. Not too long ago it would have taken a lot of effort to connect
to a network; today you open the box for a new device, turn on the power, and often see more than one
network.
When you use the ODS tagset template “EXCELXP”, the output file is a *.xml text file that Excel can open
by double-clicking on the file. This is great for many things, but SAS cannot include SAS graphs in *.xml
files. This chapter will introduce a simple 'Hello World' project that will demonstrate how to use the SAS
ODS tagset template EXCELXP to create a simple *.xml file, have SAS start Excel, use the *.xml file as
input, and then modify the file and display the resulting graph for us to examine. The only action required
on the part of the SAS user (after the initial setup of the code) is to run the SAS code. All of the Excel
activity will be pre-programmed.