creating multisheet excel files

[ucvikas]

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.

[Reeza]

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.

[ucvikas]

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.

[Reeza]

43496 - Convert files created using an ODS destination to native Excel files

[Peter_C]

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 ;

[Cynthia_sas]

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