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

● This value is used when the VBA code processes the outputs when the “Run Program”
(CommandButton2) is clicked.
• Cell B11 is a comment.
Control_info Worksheet - Group 4 - Cells C3 to D7.
● Cells C3 to C7 are not comments.
● Cell C3 is cell that holds the information about the unique parameters that are loaded and for the
program that will be executed.
● Cells C4 is part of the ComboBox list of programs and is the initial value of no program loaded.
● Cell C4 to C7 is the full list of active programs that you can choose.
● Cell C7 is an end of list marker but still part of the list area.
● Cells D4 to D7 are mixed use; some cells are used by the tool, and some cells are comments.
● Cell D3 is the cell that holds the description of the program that will be executed.
● Cell D4 is a comment.
● Cells D5-D6 are program descriptions.
• Cell D7 is a comment.
One of the features of this tool that makes it stand out is that the code is written as two *.sas files. One file
is written by the tool that contains the following:
● SAS code to preset several system options.
● SAS code to create several macro variables.
● SAS code to display the macro variable values on the SAS log.
● PROC PRINTTO statements to save the log and list outputs.
● A program-unique audit value that is set in the JOB_ xx …..sas code file and tested in the
“Job_ xx _Parm_Code.sas” file to prevent using the wrong “Job_ xx _Parm_Code.sas” and
JOB_ xx …..sas code file together.
● SAS Macro “Init_Global_Macro_Variables”.
• Code at the end of the “Job_ xx _Parm_Code.sas” includes and executes the JOB_ xx …..sas code
file.
The other *.sas file is the program that uses the definitions from the “Job_ xx _Parm_Code.sas” file to
process the current data files. When you use this method, you will find that once a program is written,
tested, and debugged if it fails in the future it is usually because the data value ranges, variables, or contents
have changed.
Figure 14.11 shows the contents of the Common_Parm_Text worksheet of the tool. This worksheet
contains the beginning and the end of a macro definition (the init_Global_Macro_Variables macro). This
macro is run at the start of each program to install both the common and program-unique SAS macro
variables. By running this code, you will ensure that the changeable code parts of the program are defined
by the user before the code is set aside for execution.