Extend the Power of SAS® to Use Callable VBS and VBA Code Files, continued
SAS can use the “X” command to start the VBS routine and assign the parameter values to process the
report. (Note the
SAS “X” command in not available to the SAS Enterprise Guide users due to environment
restrictions that prevent SAS Enterprise Guide from knowing where it is running).
6. SAS can process the data files to be used as input to the VBS control module. The output from SAS can be
any format that the VBA code modules can read.
The SAS “X” command is a powerful command tool. It allows nearly any Operating System command to be executed
by starting it from within the SAS program code. Directories can be listed, other SAS jobs can be started, Excel
workbooks can be opened, files can be deleted, and now we will discuss how a built-in operating system function can
be used to control a Microsoft product like EXCEL (and Word). The VBS scripting language is similar to the VBA code
language; but, it does have a few minor differences. You can execute the commands stored in a VBS code module
(any_file_name.vbs) simply by double clicking on the filename or using the SAS “X” command to run the script. VBS
scripts will also accept parameters at invocation, allowing you to control how they work internally. VBA macros can be
built by recording the macro from within the EXCEL workbook. These recorded macros generally have a lot of default
commands that are not required for a final macro and they also have a lot of workbook and worksheet specific cell
references that may be too specific for a general purpose macro. SAS has the feature of being a top-down
programming language, what that means is that code has to be defined before it can be used. Code is presented to
SAS as a text stream, and each macro, data step and procedure call must be executed one group at a time. Code at
the bottom of the text file is not executed until the step it is contained within is executed, usually at end of the job.
However Object Oriented programming languages like VBA read in the whole set of code routines, compile them,
then passes control of the program to a routine that waits for something to happen. Like a user moves a mouse
pointer, clicks on a menu, or pushes a key on the keyboard. Each object has its own list of things you can make it do
or do to it. These are beyond the scope of this book, but some simple things will be explored to show how to start
building a set of your own VBA macros to use to create your reports.
Virtually anything that you can do using Excel you can do using VBA, after all Excel was written using the VBA
language. So, you can modify cells by adding data to them, outlining them, moving them copying them, or clearing
them. You can add or delete worksheets, and manipulate rows or columns of data. By being able to add or delete
worksheets you have the ability to pass information to Excel that can be used in the formatting process and then be
able to delete that information, This is something that a TAGSET cannot perform. Files can be read, written or
converted from one format to another. By writing the VBA code yourself you have control of the order of the actions
that Excel takes. The intent here is to show you how to create formatted Excel output files that are ready for delivery
to your user in minutes instead of spending much longer doing it yourself. Making them high quality will be left to you.
The initial setup of the first run of each report may take a little longer because you need to write VBA code, but every
time you execute the report you fill save time running and getting it ready to print.
The SAS code listed below under the heading “
SAS Code to Create an Unformatted Output *.XML File
is a simple example. It starts with the SASHELP.CLASS file and sorts it by sex and height, then writes it to a *.xml file
using the ODS Tagset EXCELXP. We will also suppress the column with the observation number to make the file a
little cleaner. Additionally, the code sets up SAS macro variables to define the following:
VBA subroutine name to execute
C:\MY_Excel_Files\my_sorted_class_data.xml Full File path and Input file name
C:\MY_Excel_Files\my_sorted_class_data.xls Full File path and Output file name
VBA module name (without the bas)
VBA subroutine name to execute
Table 1. List of Parameters used to control the execution of the VBS script shown below.