DATA Step, Macro, Functions and more

Export excel problem using ods

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Export excel problem using ods

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.


Accepted Solutions
Solution
‎06-27-2014 11:24 PM
SAS Super FREQ
Posts: 8,743

Re: Export excel problem using ods

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: Export excel problem using ods

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.

Contributor
Posts: 53

Re: Export excel problem using ods

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'

Super User
Posts: 10,516

Re: Export excel problem using ods

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.

Solution
‎06-27-2014 11:24 PM
SAS Super FREQ
Posts: 8,743

Re: Export excel problem using ods

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

Super Contributor
Posts: 263

Re: Export excel problem using ods

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? 

Super User
Super User
Posts: 7,413

Re: Export excel problem using ods

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. 

Regular Learner
Posts: 1

Re: Export excel problem using ods

Hi 

 

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 2253 views
  • 3 likes
  • 7 in conversation