topic Export excel problem using ods in SAS Programming

Export excel problem using ods

dsbihill — Fri, 27 Jun 2014 22:07:00 GMT

When i run the following code i get the error shown in the log snipet below. I am connecting to sas through a citrix connection and wanting to export this out to my local machine. Is there an option somewhere that i am missing?

Thanks

Damon

Code

ODS TAGSETS.EXCELXP

FILE="\\Client\C$\SAS Outputs\Data Analysis.xlsx"

STYLE=minimal

OPTIONS ( Sheet_Name = 'Model' );

PROC PRINT DATA=work.tmp_user_mod; RUN;

ODS TAGSETS.EXCELXP

OPTIONS ( Sheet_Name = 'Cost V' );

PROC PRINT DATA=work.temp_cost_v; RUN;

ODS TAGSETS.EXCELXP CLOSE;

Log

ERROR: Insufficient authorization to access /server/sas/Lev1/SASApp/\\Client\C$\SAS Outputs\Data

Analysis.xlsx.

ERROR: No body file. TAGSETS.EXCELXP output will not be created.

Re: Export excel problem using ods

Haikuo — Fri, 27 Jun 2014 22:25:46 GMT

It seems to me that you don't have at least write access to that location. Try to copy a random file onto that folder to see what happens. If you can't do that, talk to you network administrator.

Re: Export excel problem using ods

dsbihill — Fri, 27 Jun 2014 22:31:09 GMT

Thats correct i dont have write access to the /server/sas/lev1/sasapp/ location but i also didn't choose to try and write here. For some reason sas is putting this in front of the location

that i want to write to that is my local client at '\\client\c$\sas outputs'

Re: Export excel problem using ods

ballardw — Fri, 27 Jun 2014 22:37:18 GMT

Since you specified a RELATIVE location in your file= that may be the issue. Try a fully qualified name such as "C:\folder\file.xlxs"

But there also may be an issue with your apparent sever not seeing that location as the result file is a mix of unix and windows filenaming. There's a question about that in the forum about every day now it seems.

Re: Export excel problem using ods

Cynthia_sas — Sat, 28 Jun 2014 03:24:05 GMT

Hi:

The fact that the resolved path is this:

/server/sas/Lev1/SASApp/\\Client\C$\SAS Outputs\Data

where the /server/sas/Lev1/SASApp location is "prepended" to the FILE= location indicates to me that

1) SAS is running on a server;

2) the default output location on the server has been "pre-defined" as /server/sas/Lev1/SASApp/ (and usually, this location is set as read-only for all the people who are using SAS on this server)

3) you will need to use a fully qualified path name, to a folder where you have write access, but it will need to be a server location that SAS can see. Generally, this will NOT be your local C drive, unless your administrators have enabled this.

Another point -- your file extension is incorrect. You cannot use .XLSX as a file extension for TAGSETS.EXCELXP. TAGSETS.EXCELXP creates Spreadsheet Markup Language XML created for Office 2003. The correct file extension is .XML, but, in a pinch, you can name the file with the extension .XLS. Excel will complain about the .XLS, but not about .XML -- I believe that if you use .XLSX, that the file will not open. Using .XLS just fools the Windows registry into launching Excel if you double click on the file. But, Excel will complain with this snarky message shown in this Tech Support note: 31956 - Using the MSOffice2k ODS destination with Microsoft Excel 2007 generates a dialog box.

So, even if you fix the server/file issue, you may still have problems unless you change the file extension.

cynthia

Re: Export excel problem using ods

andreas_lds — Mon, 30 Jun 2014 07:44:01 GMT

Cynthia@sas wrote:

Another point -- your file extension is incorrect. You cannot use .XLSX as a file extension for TAGSETS.EXCELXP.

Will there be a tagset generating xlsx files in the near future?

Re: Export excel problem using ods

RW9 — Mon, 30 Jun 2014 08:25:24 GMT

I wouldn't think so. XLSX is a Microsoft Open Office format file. What that means is that it is in essence a ZIP file (rename the XLSX to zip and then doubkle click it), with several files and folders within it. These files and folders describe the document, the sheets, links formats etc. in a many relational XML format. VBA sections are still compiled binary elements. The tagset ouput just generates a text XML file which Excel then interprets using its own XML parser. To generate an XLSX is not that simple and would require a fair bit of processing.

Re: Export excel problem using ods

thomasduhs — Fri, 13 Nov 2015 07:20:20 GMT

I have a very similar problem.

SAS is running on a remote linux server. I'm using a windows PC. I'm trying to export an excel file from SAS EG to the local PC C:\.

The sys admin is telling me that's not possible. I don't believe him though because it is possible to do it with the export file wizard, so it seems to me it *must* be possible. I just can't work out how to do it in code.

The export file wizard references "\\Client\C$\directory\subdir". When I try to write to that directory using code, it prepends the server name "\opt\prod\...". If I reverse the slash to direction the prepended server name goes away "//\\Client\C$\directory\subdir\" appears to use the right directory, but then I get an insufficient privileages message, even though I do of course have write privelages for "C:\directory\subdir".

Any thoughts? I just don't see how it's plausible that this *can't* be done, given the export wizard can do it easily.

Cheers

Thomas