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

Figure 14.11: Contents of the “Common_Parm_Text” Worksheet of the Reporting Tool.

Each of the programs installed into the tool (as it is designed here) has two spreadsheets: one for current
data, and one for backup data. The names here are simple Job_ xx _Parms and Job_ xx _Backup. We will
install a routine associated with the CommandButtons 3 and 4 to back up and restore data using the
Job_ xx _Backup worksheets.

Common_Parm_Text Worksheet - Part 1
The data on this worksheet is used to build a piece of SAS code that is used as the starting routine of the
program. This is a SAS macro that defines the global parameters that are used to define the data this project
uses. Since we are writing code to avoid having it change, we will use this macro to define the code and
directory changes needed to run the code for the current processing period, without modifying the
production code.

Column A of this worksheet
● Cell A1 has a number that is the number of lines to write to a text output file named

Job_ xx _Parm_Code.sas.

Cells A2 - A11 will be written out to the Job_ xx _Parm_Code.sas file. Yes, cells A7 to A11 are
blank; I like white space.

●

Column B of this worksheet
●

Cell B1 has a number that is the number of lines to write to a text output file named
Job_ xx _Parm_Code.sas.

This column of cells names the macro and the common parameters. You can put as many common
parameters here as you like. There are separate %Global commands so that the housekeeping is
simple and so that every line can be easily enclosed in a quoted string.

●

Cells B2-B16 will be written out to the Job_ xx _Parm_Code.sas file. (Yes, Cell B16 is blank; I like
white space).

●

Cell B3 in this column of cells turns off the macro print options (symbolgen, mlogic, and mprint),
and then prints the values of the common macro variables to the log. Turning off the macro print
options allows the macro variable values to be spotted easily within the log listing. You can put as
many common parameters here as you like. There are separate %put commands so that the
housekeeping is simple and so that every line can be easily enclosed in a quoted string.

●