Create an Excel Workbook That Runs SAS Programs - Exchanging Data Between SAS and Microsoft Excel - page 232

Table 14.1.1: Tools Described in Chapters 12, 13, and 14.

Tool Chapter Description

Personal Workbook Tool
SAS tool to run personal Excel
macros, already included in Excel
(under Macros  Record Macro 
Store macro in).

12 This tool uses the SAS “X” command to execute

Excel macros in your Personal Excel “Xstart”
directory. Allowing Excel workbooks to be
delivered without embedded macros.

Macro Library Tool
SAS tool to run externally stored Excel
macros. You can use the “Macro
Library Tool” within Excel to take the
same macro you would have created in
the Personal Workbook Tool and
export it to a departmental library.

13 This tool uses the SAS “X” command and the

Windows operating system scripting language to
control processing of Excel macros. Allowing
Excel workbooks to be delivered without
embedded macros.

Excel Workbook Tool
An Excel tool to store parameters for
SAS programs and either execute the
SAS code or place the code into a
directory for execution.
(My_Excel_Tool, available in the
example code and data folder on the
author's SAS Press page)

14 This Excel workbook tool will save parameters

for a SAS program and either execute or copy
the code to a directory. It uses features of the
other two tools and allows for storage of SAS
code in a production-type area so the original
code is not modified when the reports are
processed. .

14.2 Purpose
This approach starts with the following assumptions:

1. You store all of your “Good” [think departmental final report level] code in a source directory.
You may want to Write-protect this directory to prevent accidental updates.
2. You have a set of periodic working libraries that you can use to save your current monthly version
of code files and output data for each report.
3. The input data is also stored in a set of monthly directories that have an established naming
convention that allows systematic access with minimal path name changes. Multiple monthly
libraries can be used to write quarterly or annual reports by accessing these data files from within
the individual report programs.
4. The code in your source library uses macro variable values to provide the data file path names,
dates, variable file names, or other data to the individual reports at run time.
5. Data paths within the report programs are defined with macro variables to prevent the “hard
coding” of information that can change. This way, the programs do not change; only the names of
the data files for input or output change.
6. You write your programs using standardized macro variables, so that the Excel file or the options
on the Excel form can pass macro variables with known names and common data values to each
program. This will help standardize the programs and aid in making maintenance easier.
7. The Excel program can store segments of SAS code to set up report-unique macro variables that
will be inserted into the output files.
8. The Excel tool is set up to store SAS code in a directory. The code can then be executed by either
running SAS and executing the program or by double-clicking on a Windows batch job file (*.bat)
to execute the SAS code.
9. Each program has been set up to store the output log and list files into a disk file for audit and job
validation processing.