Help using Base SAS procedures

is it possible to export sas data into a xlsm macro-enable excel file?

Reply
Frequent Contributor
Posts: 78

is it possible to export sas data into a xlsm macro-enable excel file?

Hi there,

I have a pivot table in a macro-enabled excel file (.xlsm), what happen is that this pivot table rely on all the data in another spreadsheet call "Data".

"Data" spreadsheet gets updated every quarter manually by copying and pasting job out of a text file spit out by SAS. Usually contains 200,000 rows of data. So every time when my analyst has to go tru the copy and pasting procedure is really time consuming.

what we are hoping is that using SAS to spits out the raw data nicely into this .xlsm file sheet!Data so all we have to do is everytime we can just open the excel file right click on the pivot table > refresh and from there no more tedious updating work!

Question: Is SAS able to spit out that data into a .xlsm file? If yes, i really need help to assign the data to designated cells, column and row into sheet!data.

Please help!


yennie
SAS Super FREQ
Posts: 8,866

Re: is it possible to export sas data into a xlsm macro-enable excel file?

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
Occasional Contributor
Posts: 9

Re: is it possible to export sas data into a xlsm macro-enable excel file?

The way I do this is to use the SAS Component Language (SCL which is part of SAS/AF) to get a direct object reference to Excel then have Excel make an OLEDB connection back to SAS using the sas.IOMProvider. From there I write the SAS dataset directly into Excel's pivotcache object which eliminates the need for an Excel "data" sheet. After that I open up an Excel macro file to handle all the formatting that was saved using Excel's macro recorder.

Here is the initial SAS code to get an object reference to Excel using the sashelp.fsp.hauto class...


/* instance object reference to Excel */
hostcl = loadclass( 'sashelp.fsp.hauto' ) ;
hostcl._new( excel, 0, 'Excel.Application' ) ;
excel._setProperty( 'Visible', 'False' ) ;
excel._getProperty( 'Workbooks', workbooks ) ;
excel._setProperty( 'DisplayAlerts', 'False' ) ;
PROC Star
Posts: 7,474

Re: is it possible to export sas data into a xlsm macro-enable excel file?

And, of course, within SAS one can also always build and run VBA and/or DDE code to accomplish such tasks.
Ask a Question
Discussion stats
  • 3 replies
  • 3130 views
  • 0 likes
  • 4 in conversation