topic Re: Ods excel won't work but html does in ODS and Base Reporting

Ods excel won't work but html does

Jane7476 — Tue, 08 Sep 2015 15:06:47 GMT

Hi All, Was waiting for the sas community upgrade to finish so that I could post this. SAS enviornment: I am running EG 4.3 on a unix based server Problem: I can write ods html, rtf and pdf to a server userdata folder. However I can't write a xls or xlsx file. Code: Desired result: Produce a html file in the server userdata folder ods html body="/someserverlocation/alpha.html"; proc print data=sashelp.class; run; ods html close; Problem: Using a xls or xlsx extension causes issue ods html body="/gbm/gbmrisksys/teamdata/users/gulahar/alpha.xlsx"; proc print data=sashelp.class; run; ods html close; xls issue: if i use xls it says file format not recognized and it does produce a file xlsx issue: if i specify this then on trying to open the file it comes up with a file import wizard and the imported table just consists of the variable format information rather than the data. Question: 1. If i can write to pdf, rtf and html to the server user folder shouldn't i be able to write xls or xlsx files also? 2. When the html file gets produced on opening it i can easily import it to my EG enviornment using the file wizard. However the xlsx file only results in the variable formats getting imported. Why can't i import the xlsx file? Thanks

Re: Ods excel won't work but html does

Tom — Tue, 08 Sep 2015 15:19:27 GMT

Does not sound like you have created an XLS or XLSX file. Just an HTML file that has the wrong extension on it. Open it with Excel and re-save it as an actual XLS or XLSX file and then try usng the EG file wizard on it. Best solution would be to update SAS and EG. You will then have better tools to make XLSX files from SAS.

Re: Ods excel won't work but html does

Cynthia_sas — Tue, 08 Sep 2015 18:28:31 GMT

Hi:
ODS EXCEL was first introduced in SAS 9.4. So that will not work, depending on your version of SAS (since you have EG 4.3, you probably do NOT have SAS 9.4).

Then you use ODS HTML, you are creating an HTML file that Excel knows how to open and render. So if you simply name your file with an extension of .htm or .html, then transfer the file to the platform where you have Excel, you should be able to do a File --> Open and then a Save AS, as Tom has described.

When you use ODS HTML or ODS TAGSETS.EXCELXP to create ASCII text files (either HTML or XML), then they are not true "binary" Excel files, such as created with Office 97-2003 -- so naming the output from ODS HTML as .XLS is just a convenience so that Excel launches when you double click on the file name -- using File -->Open works just as well with HTML.

cynthia