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
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.)
Per http://support.sas.com/kb/37/485.html, there is no workaround at the time.
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;
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.
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.)
I tried to use another engine to export as xlsx file. It looks working.
DBMS=excel
This worked for me!
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 🙂
@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) ;
Amazing! Thank you.
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;
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.