BookmarkSubscribeRSS Feed
Brian_S
Fluorite | Level 6

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?

23 REPLIES 23
Shmuel
Garnet | Level 18

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

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

Ksharp
Super User

Did you try newfile= option ?

 

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

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.

 

 

Shmuel
Garnet | Level 18

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.

 

Brian_S
Fluorite | Level 6

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.

StewM
Calcite | Level 5

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!

pw182126
Fluorite | Level 6

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

Brian_S
Fluorite | Level 6

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?

Shmuel
Garnet | Level 18

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.

epp_dog
Calcite | Level 5

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.

jdaleo23
Calcite | Level 5

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?

Shmuel
Garnet | Level 18

@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?

jdaleo23
Calcite | Level 5

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.

Shmuel
Garnet | Level 18

@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 ?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 23 replies
  • 7706 views
  • 1 like
  • 8 in conversation