BookmarkSubscribeRSS Feed
Yennie
Calcite | Level 5
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
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
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
tbellmer
Fluorite | Level 6
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' ) ;
art297
Opal | Level 21
And, of course, within SAS one can also always build and run VBA and/or DDE code to accomplish such tasks.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 5395 views
  • 0 likes
  • 4 in conversation