DATA Step, Macro, Functions and more

Proc Export silently fails

Reply
New Contributor
Posts: 3

Proc Export silently fails

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?

Trusted Advisor
Posts: 1,372

Re: Proc Export silently fails

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

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

Super User
Posts: 9,676

Re: Proc Export silently fails

Did you try newfile= option ?

 

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

Re: Proc Export silently fails

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.

 

 

Trusted Advisor
Posts: 1,372

Re: Proc Export silently fails

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.

 

New Contributor
Posts: 3

Re: Proc Export silently fails

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.

Occasional Learner
Posts: 1

Re: Proc Export silently fails

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!

Ask a Question
Discussion stats
  • 6 replies
  • 215 views
  • 0 likes
  • 4 in conversation