topic Re: Clean .bak files from PROC EXPORT in SAS Programming

Clean .bak files from PROC EXPORT

felyp222 — Fri, 02 Dec 2016 10:51:44 GMT

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

Re: Clean .bak files from PROC EXPORT

Kurt_Bremser — Fri, 02 Dec 2016 11:23:23 GMT

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

Re: Clean .bak files from PROC EXPORT

Reeza — Fri, 02 Dec 2016 11:31:59 GMT

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.

Re: Clean .bak files from PROC EXPORT

Doug____ — Tue, 16 May 2017 20:33:59 GMT

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

Re: Clean .bak files from PROC EXPORT

Doug____ — Tue, 16 May 2017 20:44:20 GMT

Any updates on this?

Re: Clean .bak files from PROC EXPORT

LaurieF — Tue, 30 May 2017 04:15:25 GMT

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

Re: Clean .bak files from PROC EXPORT

Fely — Tue, 30 May 2017 05:57:29 GMT

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

Feli

Re: Clean .bak files from PROC EXPORT

Iris22 — Wed, 31 May 2017 16:39:57 GMT

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

DBMS=excel

Re: Clean .bak files from PROC EXPORT

LRH — Wed, 13 Sep 2017 16:23:49 GMT

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 🙂

Re: Clean .bak files from PROC EXPORT

Tom — Wed, 13 Sep 2017 16:33:36 GMT

@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) ;

Re: Clean .bak files from PROC EXPORT

LRH — Wed, 13 Sep 2017 17:00:52 GMT

Amazing! Thank you.

Re: Clean .bak files from PROC EXPORT

njr — Wed, 20 Jun 2018 20:27:43 GMT

This worked for me!

Re: Clean .bak files from PROC EXPORT

NormaO — Fri, 10 Aug 2018 14:38:24 GMT

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;

Re: Clean .bak files from PROC EXPORT

Reeza — Fri, 10 Aug 2018 16:55:39 GMT

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

Re: Clean .bak files from PROC EXPORT

NormaO — Fri, 10 Aug 2018 17:59:02 GMT

Thank you! I am already messing up on my first post 🙂

Re: Clean .bak files from PROC EXPORT

RLANG1152 — Mon, 22 Apr 2019 20:28:49 GMT

Here is another solution that calls cmd through SAS using the x command (file path needs to be quoted).

%macro delete_bak(xlsx_path);
option noxwait;
x del /f &xlsx_path..bak;
%mend;

Hope this helps!

Rob

Re: Clean .bak files from PROC EXPORT

LaurieF — Mon, 22 Apr 2019 20:48:03 GMT

External commands are rarely, if ever, permitted at virtually all the sites I've worked at. Since I wrote that macro particularly to deal with loading spreadsheets from within SAS/DI, the LSF userid was locked down even more, so there was no way it would be allowed. On top of that, it was a Linux site...

Re: Clean .bak files from PROC EXPORT

RLANG1152 — Tue, 23 Apr 2019 03:49:17 GMT

Fair enough... the company I work for uses a Windows based server and
allows things like this so this was the solution that I used. Thanks for
the reply and the info.

Rob

Re: Clean .bak files from PROC EXPORT

ScottBass — Sun, 08 Sep 2019 21:49:59 GMT

@LaurieF wrote:

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

Hi Laurie,

Thanks so much for this approach to this issue. It is a simple, straightforward solution to PROC COPY creating these .bak files.

If you/someone want to implement this solution as a "pure macro" approach vs. data step, there is a subtle difference:

rc = filename(fname, "&file..xlsx.bak");
rc = fdelete(&fname);  * <<<<< (needs ampersand) ;
rc = filename(fname);

Again, thanks for this idea. I have incorporated it into this macro (scroll to the bottom):

https://github.com/scottbass/SAS/blob/master/export_dbms.sas

I acknowledge the use of your ideas in this macro. Please let me know if you have any issues, as I assumed your ideas to be in the public domain by virtue of your post.

Re: Clean .bak files from PROC EXPORT

LaurieF — Fri, 06 Sep 2019 01:47:35 GMT

There’s no bug in the code. fname is a data step variable, not a macro variable. It works exactly as intended.

And you’ve got your copyright attribution on *my* code. That’s a bit **bleep**.

Laurie