CODEWITHSUNDEEP
batch-filesasexport-to-excel
Thomas Z
Thomas Z

Reputation: 1

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

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.

Upvotes: 0

Views: 2763

Answers (1)

SunnyRJ
SunnyRJ

Reputation: 393

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;

Upvotes: 0

Related Questions