topic Re: Proc Export silently fails in SAS Programming

Proc Export silently fails

Brian_S — Fri, 11 Nov 2016 21:29:22 GMT

I have a strange "error" when I use Proc Export and it doesn't always happen, only sometimes and happens in multiple programs. It's a standard Proc Export:

proc export data = dataset outfile = "C:\myfile.xlsx" dbms = Excel replace;sheet="test";run;

SAS 9.3 | Windows 7

There will be no error, the log will say it all completed successfully, but when I check the sheet (which is set up with formatting so I don't have to copy/paste from a new file) it is not updated. The only way I know how to make it work is to open the sheet, make ANY change, even 1 number in one cell, and save it. Then re-run the export and VOILA! it works, never fails.

Any ideas? Anyone else get this?

Re: Proc Export silently fails

Shmuel — Fri, 11 Nov 2016 21:41:55 GMT

Could it be that the excel file was open while exporting ?

If the file is open, update/replace will be not done.

Re: Proc Export silently fails

Ksharp — Sat, 12 Nov 2016 03:47:49 GMT

Did you try newfile= option ?

proc export data = have outfile = "/folders/myfolders/myfile.xlsx" dbms = xlsx replace;
sheet="test";
newfile=y;
run;

Re: Proc Export silently fails

Brian_S — Mon, 14 Nov 2016 16:35:24 GMT

I have considered both of these solutions.

I am sure the file is not open. I open the file daily but only after the completion of the SAS job....unless I get the error.

NEWFILE=Y deletes the file that is currently saved in that location and saves a new one and so the formatting that is on the file is lost.

It may be worth it to note that a code that is always run prior to this one saves information to a separate tab than the target tab in this proc export. I'm not sure if that would occasionally make the file appear to be open. If there is an option to set to make sure this is not the case, I'd be willing to try it. I'm not sure though if I did have the file open, if it would error out completely.

Re: Proc Export silently fails

Shmuel — Mon, 14 Nov 2016 19:40:12 GMT

I understand that you have two steps, each update different tab in same excel file.

You may try do:

filename xxxx excel '...path and name ...';

proc export /* update tab1 */

filename xxxx clear; /* with hope that will close and release the file to another update */

filename xxxx excel '...path and name ...';

proc export /* update tab2 */

I'm curious to know, does it help.

Re: Proc Export silently fails

Brian_S — Thu, 29 Dec 2016 17:17:55 GMT

Unfortunately this did not help. Additional piece of information. The change of the number in the Excel file is not even necessary. I just open it and press the save disk icon and close it and it exports fine after that. It's a strange issue.

Re: Proc Export silently fails

StewM — Tue, 11 Jul 2017 16:07:42 GMT

I also have the same issue when exporting to formatted excel files - I'm outputting reports as datasheets in the excel file which are then pulled by formatted presentation tables. The log will indicate a successful export but on opening the output there are blank pages. I'm hoping that there is a solution as I have this within a Macro which produces in excess of 100 reports, however I'm not hopeful give the recent silence on this silent error!

Re: Proc Export silently fails

pw182126 — Mon, 11 Sep 2017 12:58:06 GMT

I have the same issue!

Our report update script is scheduled on a monthly basis.

Couple of month in a row the update with "proc export" work just fine, but than it fails "silently".

I already have written a macro which compares the time stamp of the file and the current time.

If the difference is more than 5 sec (for example) after an update I do abort with an error message.

But it still doesn't solve the problem!

I tried everything, like writing a blank data set into the spreadsheet.

I also tried to delete the data set with proc datasets delete.

In both cases the file doesn't get updated or chaged and process fails silently.

Only option is to change the file manually.

Unfortunately it's not a solution as we want to eliminate manual intervention with the process.

Help?!

Re: Proc Export silently fails

Brian_S — Mon, 07 May 2018 16:19:14 GMT

It's been awhile, and this problem still presents itself. We have since moved to 9.4. It is completely dependent on the file itself. The timestamp remains unchanged. No prior processes stop it, this code has a single proc export. Anyone else found a way to correct this?

Re: Proc Export silently fails

Shmuel — Mon, 07 May 2018 16:44:04 GMT

Just a guess - try export only part of dataset using options obs and check is failure dependent on -

either amount of observations or on data in a specific obs.

Re: Proc Export silently fails

epp_dog — Mon, 21 May 2018 14:51:50 GMT

I have the same issue with a "silent fail" writing to an existing spreadsheet/named range where the corrective action is to manually open/save the file. That makes me think that this is a problem with a cached version of the file or metadata information perhaps and the manual save is refreshing/replacing the cached version? (just guessing there) My master spreadsheet is in a protected folder so no users can open it so it doesn't get touched by any SAS procedure or myself between runs (don't know what windows/antivirus stuff happens between runs though).

I posted a similar question several months ago and all the suggestions were on ways to identify that the spreadsheet has not updated (which we have now done to prevent incorrect reports from going to users but doesn't really fix the issue.)

I wonder if issuing a system command to open/save the file right before the export would help fix this (we technically use a libname statement with data steps writing to the existing named ranges after deleting the data from those ranges) . Problem is that I have yet found a way to reliably reproduce the error so I could test this as it can be months between this error happening.

Re: Proc Export silently fails

jdaleo23 — Fri, 10 Aug 2018 14:37:41 GMT

I'm having the same issue exporting daily data. It will run for 3-4 days, then just stop updating the file. I'll go into it, delete a cell, save and close. Then run the export, and it's fine for another 3-4 days. Nobody else has access to the location, and it's being updated at 4am, so i know nobody else is touching it.

I'm just wondering if you've gotten anywhere with this, or had any new ideas?

There are other sheets in the spreadsheet, so i can't delete the file itself, wondering if there is a command to delete just the sheet, so it's technically creating a new sheet every day?

Re: Proc Export silently fails

Shmuel — Fri, 10 Aug 2018 18:54:00 GMT

@jdaleo23 wrote: "I'll go into it, delete a cell, save and close.".

Is there any criterion you used to choose the cell to delete ? which afterwards it worked fine?

Re: Proc Export silently fails

jdaleo23 — Fri, 10 Aug 2018 18:56:16 GMT

No, I can delete or modify any single cell, A1, B4, C12.. it doesn't matter. But once I make that change, save and close.. it'll export and replace with the correct data again for a few days.

Re: Proc Export silently fails

Shmuel — Fri, 10 Aug 2018 19:04:35 GMT

@jdaleo23 wrote:

No, I can delete or modify any single cell, A1, B4, C12.. it doesn't matter. But once I make that change, save and close.. it'll export and replace with the correct data again for a few days.

Is there any message or hint in the log ? can you post it ?

Re: Proc Export silently fails

Shmuel — Fri, 10 Aug 2018 19:05:10 GMT

I mean the log of the failed run.

Re: Proc Export silently fails

jdaleo23 — Fri, 10 Aug 2018 19:07:34 GMT

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.

Re: Proc Export silently fails

pw182126 — Fri, 10 Aug 2018 20:56:18 GMT

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! 😉

Re: Proc Export silently fails

jdaleo23 — Sun, 12 Aug 2018 17:51:03 GMT

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?

Re: Proc Export silently fails

epp_dog — Mon, 13 Aug 2018 14:25:41 GMT

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).