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

Table 12.3.1: Tasks to Create a “Hello World” Project.

Task System Comments

Create SAS program SAS Use ODS tagset EXCELXP to create a *.xml file with a label

containing “Greetings World” in cell A1 and “Hello” in cell A2.

Create an Excel macro Excel Record a personal Excel macro to write “World” in cell B2.

Create a VBA code
module

Excel Write a personal VBA code module to run the Excel macro.

Run SAS program to
create the *.xml file

SAS Create the input *.xml file to be sent to Excel.

Run Excel using *.xml
file as input

SAS Use the SAS “X” command to start Excel.

12.3.1 Prepare a SAS File and Execute Excel to Process the Output
The first thing we need to do is create a simple SAS program to run the ODS tagset EXCELXP. This step
will generate a *.xml file that we can open with Excel by using the SAS “X command”. All we need is one
variable and one observation. We will use PROC PRINT to add a LABEL to the variable. The following
SAS code will create the SAS dataset, open the *.xml file using ODS, print the data to the *.xml file, and
open the *.xml file using Excel.

Figure 12.3.1: SAS Code to Create and Open an Excel File.
** Create a simple SAS File **;
DATA Hello_to_the_world;
Greeting = 'Hello';
RUN ;

** Default paths to program Excel.exe - your path may vary **;
* 2003 = C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE;
* 2007 = C:\Program Files(x86)\Microsoft Office\Office12\EXCEL.EXE;
* 2010 = C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE;
* 2013 = C:\Program Files\Microsoft Office 15\root\office15\EXCEL.EXE;

** Start ODS and name the output xml file **;
ODS TAGSETS.EXCELXP FILE="c:\my_excel_files\Hello_world_test.xml";

** Set up ods options to call the print procedure **;
ODS TAGSETS.EXCELXP OPTIONS(Sheet_Name='Greeting') Style=minimal;

** Create an xml file using ods output and proc print **;
PROC PRINT data = Hello_to_the_world
label split = '*' noobs;

Label greeting = 'Greetings*World';
* increase row 1 font size;
Var greeting / style(head) = {Font_Size = 2 };
RUN ;

** Close the ExcelXP tagset to release the output file **;
ODS TAGSETS.EXCELXP close;

** Choose a version of Excel to run - pick default path **;
** NOTE The file name needs to be quoted if it contains spaces, etc **;

* Excel 2013 64-Bit;
X ' "C:\Program Files\Microsoft Office 15\root\office15\EXCEL.EXE"
"c:\my_excel_files\Hello_world_test.xml" ';

RUN;