BookmarkSubscribeRSS Feed
ucvikas
Obsidian | Level 7

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.

5 REPLIES 5
Reeza
Super User

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
Obsidian | Level 7

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.


Peter_C
Rhodochrosite | Level 12

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
SAS Super FREQ

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

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!

Discussion stats
  • 5 replies
  • 1272 views
  • 0 likes
  • 4 in conversation