ODS and Excel

[deleted_user]

I create reports using ODS and html (I work with base SAS under UNIX) so they can be open using Excel. Once the user opens the file they are not able to reformat the columns, i.e. if a column containing date values is highlighted and then click on format, the way the date is displayed can be changed to whatever format Excel offers. On the other hand, if this is done on an html file created thru SAS ODS, nothing will happen. The values do not change at all. Is there a way to avoid this??? The user can change the formats as he/she pleases w/o the need of playing too much with the file.

Thank you.

[Cynthia_sas]

Hi!
That is very strange! When I create these 5 files with ODS using the code below,

[pre]
ods html(id=1) file='c:\temp\class_ht4.html' style=sasweb; /* HTML 4 file */
ods html(id=2) file='c:\temp\class.xls' style=sasweb; /* HTML 4 file */

ods html3 file='c:\temp\class_ht3.xls' style=sasweb; /* HTML 3.2 file */
ods msoffice2k file='c:\temp\class_mso.xls' style=sasweb; /* MS-HTML file */
ods tagsets.excelxp file='c:\temp\class_xp.xls' style=sasweb; /* MS SpreadsheetML file */

proc print data=sashelp.class;
var name age height;
run;

ods _all_ close;
[/pre]

and then go to open the created files, I can use the Excel formatting menu to change a whole row or a whole column -- on any of the files -- no matter which ODS destination I used to create the output file. For the last 4 files, when I give a file extension of .XLS, I am just "fooling" the Windows registry into launching Excel when I double click on the files in Windows Explorer. I am not creating a "true binary" Excel file -- just an HTML or XML file that Excel knows how to render when the file is opened.

It almost sounds like your file is write protected somehow, either on the operating system end, or on the Excel end. Or, it sounds like you may be trying to open the file before ODS has closed the file, somehow. Make sure that you have the proper ODS <dest> CLOSE; or ODS _ALL_ CLOSE; as shown above.

Your best bet for help with this is to contact Tech Support.

cynthia