02-18-2014 10:52 AM
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.
02-18-2014 11:06 AM
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.
02-21-2014 02:24 PM
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 ;
proc append base= xlb.summary data= yoursummarydata ;
Libnane xkb clear ;
02-22-2014 04:27 PM
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 ).