Hi:
The only SAS methods that I know of to write to an Excel file are:
1) PROC EXPORT and the SAS LIBNAME engine for Excel which write to .XLS or .XLSX (if you have SAS 9.2) file types
2) ODS CSV or ODS CSVALL -- which creates a comma-separated value file that Excel can open
3) HTML-based methods -- ODS HTML or ODS MSOFFICE2K which create an HTML file that Excel can open
4) XML-based methods -- ODS TAGSETS.EXCELXP which creates an XML (Spreadsheet Markup Language XML) file that Excel can open
5) HTML + JavaScript or VB.NET method -- ODS TAGSETS.TABLEEDITOR -- creates an HTML file with embedded code to write the output file to Excel
You may be able to write to an XLSM file with DDE, ODBC or OLEDB methods.
However, you may want to investigate the TAGSETS.TABLEEDITOR method, as it says in the doc that you can load the HTML data into a Pivot Table as shown here:
http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/index.html#pivot
cynthia