sas and excel file name, two dots

[geneshackman]

Hi all

Wondering, we were using SAS to generate an excel file, and part of the code was this

ods html file="\\mynetworkname\myworkingdir\testthisfile.xls";

Got this error
ERROR: Physical file does not exist, C:\Users\gxs03\AppData\Local\Temp\SAS Temporary Files\_TD5580_CCHPHIG070002_\\\mynetworkname\myworkingdir\testthisfile.xls.
ERROR: No body file. HTML output will not be created.

But if we ran exactly the same program with two dots in the excel file name "\myworkingdir\testthisfile..xls" and no other change, then it worked. Any ideas why?

Thanks

Gene

[ballardw · Accepted solution]

With HTML it works best to explicitly set the Path and the file separately. The first error is because without a path setting SAS used the default which was your work library.

ods html path="<path specification>" file="testthisfile.xls" ; should avoid the issue completely as that says to put the File in the directory specified by the path. I can't put your text in there as this form software tries to turn it into a link and loses syntax elements.

[Kurt_Bremser]

Sorry, but this:

ods html file="\\mynetworkname\myworkingdir\testthisfile.xls";

is plain stupid.

If you create HTML content, use a .html extension to avoid confusion. Simply naming it .xls does not create an Excel file! Some Excel versions even complain when the content does not correspond to the filename extension.

ODS HTML is very powerful, but also needs additional data like base URL for links, path(s) to store multiple elements, and so on. Use ODS HTML when creating data for webservers.

If you need to export formatted data to Excel, I recommend using ODS TAGSETS.EXCELXP and a .xml extension on the filename.

If you "only" want to transfer data, SAS 9.4 provides much better options to create/write .xlsx files: SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition

[geneshackman]

Ballardw's response worked fine. I also found that two dots or one dot both worked, so kept with one dot and ballardw's solution.

Despite saying I'm stupid, Kurt also appears to be correct. The program writes output as html, not an excel file. I'd also be interested in learning how to get output to actually go to excel, rather than html.

Thanks to both.

[Kurt_Bremser]

I didn' call you stupid, sometimes very intelligent people can do something stupid by accident and need to have their attention called to it.