Proc Export silently fails

[Shmuel]

I mean the log of the failed run.

[jdaleo23]

I'll send a good and bad log, when it fails again.. which should be tomorrow or Sunday, since it usually fails after one successful run.

[pw182126]

Couple of month ago our customer migrated to SAS 9.4, so I changed the proc export from dbms=excel to dbms=xlsx.

The problem solved!

I'm afraid analysing the logs won't help.

There is not even a little hint what could cause the issue.

The problem occur very random

The only solution, what worked for us, was to analyse the timestamp and abort the process, then delete some data in excel file, save and rerun the process again.

Good luck! 😉 

[jdaleo23]

Thanks for the update!

I'll have to see if I can get dbms=xlsx to work, although the only issue I have been running into with that, is that I cannot get multiple sheets to write using that dbms. It basically clears the entire document and rewrites just the one sheet.  My output file has a few sheets in it.

Any ideas on that? Is that a 9.3 issue?

[epp_dog]

I believe the DBMS=XLSX engine does not support named ranges in Excel - which my pivot tables use so that is not an option.  I have good macros to check the spreadsheet after SAS thinks it writes to it and compare to the SAS datasets used to write to those named ranges.  If they don't match, we send the programmer/admin a text to open/save/re-write the data.  It is a 5 minute fix but annoying to have to do at all.  My VB is not very good (i.e. really BAD) but I am thinking that if I can write a VB script to execute from within the SAS program, I can just always open/save/close the spreadsheet before attempting the write. 

It is really strange as I have some reports where this seems to happen 70-80% of the time and others where it seems to only happen 10-20% of the time.  And I have never been able to force the error to happen either (tried just looping a open/write/close thousands of times to see if it could hit this error).  

[pw182126]

Sorry, what do you mean with "XLSX engine does not support named ranges"? I can read and write any data and from/to different sheets.

proc import datafile=&excelFile

     out=xlsdata dbms=xlsx replace;

     sheet=Basedata;

run;

proc export data=&data

     dbms=xlsx

     outfile=&excelFile

     replace;

     sheet=Basedata;

run;

[epp_dog]

Writing to different sheets is not the same as writing to different named ranges.  There can be several named ranges on the same worksheet.  That is what is not currently supported.  

We build Excel pivot tables off the the output from SAS.  In order for the pivot table to automatically grow/shrink with the raw data we reference named ranges in the pivot table definition.  (I know we could reference the entire columns of raw data for the pivot table and then unselect the "BLANKS" but 1) bad user experience and 2) doesn't solve for when we output multiple ranges in the same sheet).

Glad people are still thinking about this issue.  It is strange that I have some spreadsheets that have this problem 2-3 times a week and others that go months between problems.  I just don't see the pattern yet.

from:  https://exceljet.net/named-ranges

A named range is just a human-readable name for a range of cells in Excel. For example, if I name the range A1:A100 "data", I can use MAX to get the maximum value with a simple formula:

 =MAX(data) // max value

[XabiAlonso]

So glad someone else experienced this! My case seems to be specifically related to Pivot table. if the .xlsx has one that links to raw data, proc export doesn't work. Sharing here.

OS: Win 7

SAS version: SAS 9.4 x64

Excel version: Excel 2010, x32

Engine: dbms=xlsx

What works:

• If I create a simple xlsx file, proc export to replace content of one tab. IT WORKS, the tab is replaced with the latest data.

What doesn't work:

• If I created a simple xlsx file with raw data in tab A, and a pivot table in tab B that reads from tab A, then proc export data to tab A, it DOES NOT work.
• SAS will say proc export is successful, but it does not update raw data in tab A, and it messes up pivot in tab B to the point it is not readable.
• NOTE: the last modified date of the xlsx file is updated, and the filesize is changed to roughly what I would have expected. But the .xlsx file content does not reflect that!
• NOTE: To confirm, I did close down Excel before running proc export. All Excel windows in fact.
• THOUGHT: Is it possible that it's because I'm on x64 SAS and x32 Excel?? This I can't test.

[epp_dog]

That is interesting.  Slightly different from what I am experiencing where the actual source data does not change.  the save date is not updated.  nothing.  just SAS thinking it wrote out correctly.  And the real problem is that for me it is not consistent.  Sometimes it will work and other times it will not.  I am also outputting data to a raw named range that is referenced by the pivot table (that is important if you are not building your pivot table that way I would suggest changing). 

So far I am still stuck with saving the SAS dataset that I am writing to Excel.  Checking Excel to see if it actually updated. And then building an abort process if it did not update. Not a fix but it does prevent me from sending bad data to users.