Clean .bak files from PROC EXPORT

[felyp222]

Hi all,

I want to export a data set to "sheet1" into an excel file and if the data already exists in "sheet1", then replace it.

If the file is replaced, then a backup file is generated.

In location c: I will have a file 'test.xls' and 'test.xls.bak'.

PROC EXPORT DATA=data1

OUTFILE="c:\test.xls"

DBMS=xls REPLACE;

SHEET="sheet1";

 Is there a way to to stop it from doing the backups? Thank you, Fp

[Kurt_Bremser]

Per http://support.sas.com/kb/37/485.html, there is no workaround at the time.

[Doug____]

Kurt have there been any updates on this issue? It is a bit of a pain in a version-controlled environment.

[elenarojco]

Thank you for the link, @Kurt_Bremser !

I use now engine EXCEL instead of XLSX - no .bak files after exporting:

Proc export data=one dbms=excel REPLACE outfile="two.xlsx";
sheet=&Table.;
run;

[Reeza]

Does the same thing happen if you create an XLSX file instead? 

And can you use FDELETE () to manually clean up, since you know it will occur. 

[Doug____]

Any updates on this?

[LaurieF · Accepted solution]

I just found myself in the same position, so I wrote a macro for it (assuming that file is the name of the spreadsheet without the .xlsx suffix:

%macro xlsx_bak_delete(file=) / des='Delete backup spreadsheets';
option mprint notes;
data _null_;
fname = 'todelete';
rc = filename(fname, "&file..xlsx.bak");
rc = fdelete(fname);
rc = filename(fname);
run;
%mend xlsx_bak_delete;

It does no checking whether the file exists, and doesn't put out any fancy messages. It just does what you want.

(Obviously, if you're using xls, modify to suit.)

[Fely]

Thank you so much!
This is what I was looking for

Feli

[Iris22]

I tried to use another engine to export as xlsx file. It looks working.

DBMS=excel

[njr]

This worked for me!

[LRH]

I apologize in advance but I am still pretty new at SAS. I can't get this macro to work.

Assuming my file name is "noobie", can you tell me what I need to replace in the macro?  Again, my apologies but I have .bak files everywhere and they are driving me insane!

Any help is appreciated 🙂

[Tom]

---

@LRH wrote:

I apologize in advance but I am still pretty new at SAS. I can't get this macro to work.

Assuming my file name is "noobie", can you tell me what I need to replace in the macro?  Again, my apologies but I have .bak files everywhere and they are driving me insane!

Any help is appreciated 🙂

---

You don't replace anything in the macro (actually REPLACING things is what macros are desgined to do 🙂 ).

You just CALL the macro with the name of your file.  So if you call it like this:

%xlsx_bak_delete(file=noobie) ;

It will try to delete a file named "noobie.xlsx.bak".

If the file is not in the current working directory (of the SAS process that is running your macro call) then you might need to add a path. For example if you have been creating your XLSX files on a shared foder using SAS running on Windows then your call might look like this:

%xlsx_bak_delete(file=\\servername\sharename\foldername\noobie) ;

[LRH]

Amazing! Thank you.

[NormaO]

I am apparently even more of a novice than the individual in the prior post. My code is below. It runs without error, but does not delete the file. Any insight would really be appreciated.

%macro xlsx_bak_delete (file="\\high21\sys\GROUP\ACTUARY\Dividend\20180701 Dividend\Dividend_Data") / des = 'Delete Backup Spreadsheets';
option mprint notes;
data _null_;
fname = 'todelete';
rc = filename(fname, "&file..xlsx.bak");
rc = fdelete(fname);
rc = filename(fname);
run;

%mend xlsx_bak_delete;

[Reeza]

@NormaO it helps if you post as a new thread so everyone can see this post. Otherwise, only users who previously interacted with this thread will see it. 

Change the data _null_ to have a data set name and check the RC values. 

%macro xlsx_bak_delete (file="\\high21\sys\GROUP\ACTUARY\Dividend\20180701 Dividend\Dividend_Data") / des = 'Delete Backup Spreadsheets';
option mprint notes;
data demo;
fname = 'todelete';
rc = filename(fname, "&file..xlsx.bak");
rc = fdelete(fname);
rc = filename(fname);
run;
 
%mend xlsx_bak_delete;

proc print data=demo;run;