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

12.4.2 Create a SAS Output File with More Data and Control Information
Since we do not want every Excel workbook to have “World” in cell “B2,” the next step is to enhance the
Excel macro “ WORKBOOK_OPEN ” so that it is more selective when it runs. So, we will take out the
message boxes and add in control language to interpret information sent from SAS. By sending commands
to Excel from SAS, we can control what actions our Excel macro takes when it gets control of the system.
First, let's upgrade our SAS code.
Figure 12.4.7a: New SAS Code to Summarize the SASHELP.SHOES Dataset and Send the
Data to Excel.
** Total sales by region, and put it into a file **;
Proc summary data= SASHELP.Shoes nway;
weight sales;
var sales;
output out=sales(where=(_stat_ = "SUMWGT"));
by region;
run;
** Create path macro variable to 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\Pie_Chart_test.xml";
** Set up ods options to call the print procedure **;
ODS TAGSETS.EXCELXP OPTIONS(Sheet_Name='Greeting') Style=minimal;
** Run Proc print using labels and noobs. **;
PROC PRINT data = sales noobs label;
* add control value to the label value for Excel to use here;
label region = "?graph_1?Region";
var region sales ;
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\Pie_Chart_test.xml" ';
RUN;
The code in Figure 12.4.7a has been upgraded to do the following:
● Use PROC SUMMARY to create an NWAY summary of the SASHELP.SHOES dataset. The
output SAS work file is weighted by the variable SALES.
● Open ODS with the TAGSET EXCELXP to output data to file
C:\My_Excel_Files\Pie_Chart_test.xml.
● Use PROC PRINT to write the SAS dataset to the *.xml test file. Set up the label that the phrase
"?graph_1?Region" is placed into the Excel cell A1. The “?graph_1? is information we will pass
to the Excel macro.
• Use the SAS “X” command to run Excel and open C:\My_Excel_Files\Pie_Chart_test.xml.