Why do I get an extra '/' in my file path in xlsx export from SAS? (via batch)

0
votes

I keep getting stuck with exporting files in SAS. It's an issue that I thought I resolved, then not, then yes, etc.. anyways, here's my problem. When I want to export a .xlsx file like so

...
options validvarname=any;
filename xlsxfile "/path/file.xlsx";

proc export data=work.mydata
  dbms=xlsx
  outfile=xlsxfile replace;
  sheet="historical data";
run;

the following happens. It runs fine when I do it "by hand", i.e. just run the program. However, when I do it via batch I get

Error creating XLSX file -> /path//file.xlsx .  It is either not an Excel spreadsheet or it is damaged.   Error code=8000101D

I used like five different ways to export with macros, filename (like here) or directly addressing the file in the proc export statement. Inserting %put _user_; showed that the issue is not the macro/filename but really the proc export part.

Regarding solutions/explanations for similar problems I found on the interweb, e.g. here or there, the (excel) file is not open nor is it too large (quite the opposite).

To add a bit of confusion (or hints?) I must say that I stick to the filename version for now because it did run from time to time. But I have no clue as to why; some days the batch works, sometimes not. The code is always the same.

Could it be a hidden problem with access rights? When I try to export .xls, .csv or .txt I get ERROR: Insufficient authorization to access /path/file.*.

Again, these problems arise only when I use the batch.

I need the code to run with the batch because I don't want to show up at work at 5am just to press F3. Any help would be appreciated, also regarding where/what I could check in the batch file (although that might be a bit above my head, and authority) or what else I should check in the log files. Thanks!

v2: Imho the extra '/' is a symptom, not the problem. I'm asking for tips and tricks to make SAS give a different error message that would help find the actual issue.

v3: Thanks for all the comments. I'm more and more convinced it is a permission issue. I tried Quentins approach and export to the work directory works. I opened a line with our IT department now too to see what they come up with.

1
batch-filesasexport-to-excel
please remove the excel tag, since this is not an excel problem. - Luuklag
Very odd. What operating system? What version of SAS? Are you submitting interactively via Enterprise Guide or PC SAS ("Display Manager") or another client? How are you batch submitting? I would make a program with just the PROC EXPORT step and see if that will work. e.g. options validvarname=any; filename xlsxfile "d:/junk/file.xlsx"; proc export data=sashelp.class dbms=xlsx outfile=xlsxfile replace; sheet="historical data"; run; - Quentin
I am working with Windows but SAS runs on UNIX. I export to a shared folder (so no choice as to where to export to). SAS is EG 9.4. - Thomas Z
@Quentin Regarding batch my knowledge gets a bit thinned out. There's a (SAS) program running in the background checking if any of the programs in /batch/ are due to run, i.e. appear in schedule.sas with the right time. The "batch program" uses the credentials (pwd etc.) of another user. But he has no problems running the file "by hand" either. - Thomas Z
What user does the batch run under? Is it a different user to your personal login? It could be that the user running the batch does not have the necessary access rights (user/group access) to that location. - Robert Penridge

1 Answers

0
votes

I've had similar problems with file names getting extra slashes when I try to use PROC IMPORT. And like you, I will try different things that seem to work, and then later in batch mode the same code won't work. I don't know what the root issue is. One workaround I've found is to put the file name in a macro variable and then refer to that macro variable in PROC IMPORT / EXPORT - like this:

%let xlsxfile=/path/file.xlsx;

proc export data=work.mydata
  dbms=xlsx
  outfile="&xlsxfile" replace;
  sheet="historical data";
run;