Clean .bak files from PROC EXPORT

[NormaO]

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

[ScottBass]

---

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

[LaurieF]

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

[ScottBass]

Hi Laurie,

You're absolutely right about the copyright attribution.  It was a cut-and-paste error from my standard macro header, for which I sincerely apologize.  Really...I apologize. 

I always try to give full attribution to original authors of code, or code portions (see %seplist, %export, %export_dlm, and %parmv, among others).  Your code is in fact 8 lines of a 500+ line macro - I do consider the rest of the macro to be derived independently of your post.  But, again, I was wrong for not giving you attribution for your approach to .bak files created by PROC COPY.

I only include the copyright notice because I wish to make it crystal clear that any code I put in that repository to be freely available to anyone who wants to use it.  AFAIK, it is a recommended approach by GitHub to give clarity to code reuse by others.

I have corrected the header, giving full attribution to your original post.  Please have a look, and if you wish any further changes, please let me know.  I assumed your approach to be in the public domain by virtue of your post to this forum.  If you disagree, let me know, preferably via PM.

I was also incorrect about the data step vs. macro variable difference.  I must have been having a bad day :-/.  I have corrected my post.

Regardless, thanks for your solution to the OP.  It gave me the idea to solve my issue via my macro implementation of your original idea.

[RLANG1152]

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

[LaurieF]

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

[RLANG1152]

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

[aewalke3]

My fun solution:

Data Tables;
Infile datalines;
Input Tables :$10.;
Datalines;
Table1
Table2
Table3
;

Data Ex;
Infile datalines;
Input Var1 :2.;
Datalines;
1
2
3
;

%LET Project=C:\_____\Desktop\ODS Output;

Data _NULL_;
set Tables end=No_more;
call SYMPUTX(CATT('T',_N_),Tables);
if no_more THEN CALL SYMPUTX('N',_N_);
Run;

%MACRO Ex;
%Do a=1 %to &N;

Libname out XLSX "&Project.\&&T&a...xlsx";

Data out.Data;
set ex;
Run;

Data _NULL_;
if (fileexist("&project.\&&T&a...xlsx.bak")) then do;
FileRef=filename('d',"&project.\&&T&a...xlsx.bak");
fileref=fdelete('d');
end;
Run;

Libname out clear;

%END;

%MEND Ex;


%Ex;