SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

creating multisheet excel files

Reply
Contributor
Posts: 56

creating multisheet excel files

Hi

I have outputs from proc summary procedure . I want to output them in single excel file with multiple sheets in it ; is there any way we can do this except ODS TAGSETS ?

if please give references as well.

The reason I do not want to use ODS TAGSET is the warning "file is in a different file format than its extension indicates "it gives while opening the file with office 2010. and I do not want to create .xml files to get rid of the warning.

Grand Advisor
Posts: 17,428

Re: creating multisheet excel files

Generally 2 workarounds:

1. Use ODS Tagsets coupled with a VBS script to convert the file to a native XLSX file.

2. Grab the output into datasets and proc export them out manually.  You don't have any formatting/style when using this method.

Contributor
Posts: 56

Re: creating multisheet excel files

Hi Reeza

1;Do you have any example which can help me customize as per my requirement? I am not aware of VBS script which can do this work.


Grand Advisor
Posts: 17,428

Re: creating multisheet excel files

Valued Guide
Posts: 2,174

Re: creating multisheet excel files

If the excel file already exists (office 2003), create a range name (say, summary) that matches the data in your proc summary output.

Libname xlb excel "your workbook.xls" ver=2003 ;

proc datasets mt= data  nolist ;

Delete summary ;

Run;

Quit;

proc append base= xlb.summary data= yoursummarydata ;

Run ;

Libnane xkb clear ;

SAS Super FREQ
Posts: 8,721

Re: creating multisheet excel files

Hi, the warning is a MICROSOFT warning - not a SAS warning. Microsoft Office warns you when you open the file that the file extension you are using is not the same as the contents of the file, which is true if you do something like make an HTML file and call it with the .XLS file extension or create an XML file and name it with the XLS file extension. The XML file created by TAGSETS.EXCELXP is perfectly good and conforms to the Microsoft specification for Spreadsheet Markup Language 2003. In fact, if you name the file with an.XML file extension, you will not get that warning. The only time you get the warning is when you try to "fool" Microsoft by naming the file as .XLS. And, BTW, the fix for NOT getting that warning is a Windows registry fix (http://support.sas.com/kb/35/581.html and http://support.sas.com/kb/31/956.html ).

cynthia

Ask a Question
Discussion stats
  • 5 replies
  • 547 views
  • 0 likes
  • 4 in conversation