Thank you! I am already messing up on my first post 🙂
@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.
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.
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
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...
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.