Inconsistencies using proc export (the dreaded //)

[robulon]

Hi,

I'm having a very frustrating issue using proc export. I've seen that a lot of people have had similar issues but I've not found anything that is quite the same as this.

I'm using Enterprise Guide 7.13 and am trying to export data to an excel file. In the process flow where my data originates, the export code produces an error: -

40 proc export data = rollrate.new_to_one_plus outfile = "/share/new_template.xlsx" dbms = xlsx

40 ! replace;

41 sheet = NEW_TO_ONE_PLUS;

42 run;

Error creating XLSX file -> /share//new_template.xlsx . It is either not an Excel spreadsheet or it is

damaged. Error code=8000101D

Requested Output File is Invalid

ERROR: Export unsuccessful. See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE EXPORT used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

However if I run exactly the same code in a separate instance of EG where I have successfully used proc export before, it works: -

100 proc export data = rollrate.new_to_one_plus outfile = "/share/new_template.xlsx" dbms = xlsx

100 ! replace;

101 sheet = NEW_TO_ONE_PLUS;

102 run;

NOTE: The export data set has 3 observations and 53 variables.

NOTE: "/share/new_template.xlsx" file was successfully created.

NOTE: PROCEDURE EXPORT used (Total process time):

Both instances of EG are connected to the same server and I have tried opening a new instance of EG in case it was flawed in some way but I get the same outcome each time. This is really annoying as I just can't fathom out why this would be happening so just wondering if anyone had any ideas. I've closed Excel down completely to make sure the file isn't somehow open behind the scenes but again, this makes no difference.

Thank you,

Rob

[VDD]

I have had the same issue in the past, how I have solved it is that I open a new Excel workbook and just leave Excel running then ran the SAS export and it worked.

[robulon]

Thanks VDD,
I've given that a go but am still having the same issue.

Thanks for the suggestion though

[ChrisHemedinger]

Is it possible that the XLSX file is held open? By Excel or by SAS with a LIBNAME statement?  SAS really wants exclusive access to that file when you're reading or writing it.

[robulon]

Thanks Chris, I'm not using a libname statement, just the outfile, and I have had the issue when Excel is not open. Having said that, if I only have a single instance of EG open, it works, so I can only assume it was somehow being held open in some way.

[Reeza]

Are both instances the same version exactly? You can verify that with PROC PRODUCT_STATUS if needed. XLSX is a newer DBMS and some older versions could be problematic.

[robulon]

Reeza, both instances are the same version exactly, and were opened using the same EG in my start menu.

[SASKiwi]

You get an error code 80001019 if another process has the spreadsheet open:

https://communities.sas.com/t5/SAS-Programming/Reading-Microsoft-Excel-Data-with-SAS-ACCESS-LIBNAME-...

I suggest you contact SAS Tech Support of an explanation of your code 8000101D as I cannot find ant reference to it.

[robulon]

Thanks SASKiwi, I think I've managed to find a way around the issue (without fully understanding how it was caused).

[robulon]

Thank you all for you help and input once again.

It never fails to amaze me how many of you are prepared to take the time out to assist us less knowledgeable SAS users, and it really is hugely appreciated.

Rob