DATA Step, Macro, Functions and more

Clean .bak files from PROC EXPORT

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Clean .bak files from PROC EXPORT

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


Accepted Solutions
Solution
‎05-30-2017 01:58 AM
Super Contributor
Posts: 253

Re: Clean .bak files from PROC EXPORT

[ Edited ]

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

View solution in original post


All Replies
Super User
Posts: 8,359

Re: Clean .bak files from PROC EXPORT

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 30

Re: Clean .bak files from PROC EXPORT

Posted in reply to KurtBremser
Kurt have there been any updates on this issue? It is a bit of a pain in a version-controlled environment.
Super User
Posts: 20,731

Re: Clean .bak files from PROC EXPORT

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. 

Contributor
Posts: 30

Re: Clean .bak files from PROC EXPORT

Any updates on this?
Solution
‎05-30-2017 01:58 AM
Super Contributor
Posts: 253

Re: Clean .bak files from PROC EXPORT

[ Edited ]

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

Frequent Learner
Posts: 1

Re: Clean .bak files from PROC EXPORT

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

Feli
Senior User
Posts: 1

Re: Clean .bak files from PROC EXPORT

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

 

DBMS=excel

New Contributor LRH
New Contributor
Posts: 2

Re: Clean .bak files from PROC EXPORT

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

Super User
Super User
Posts: 7,260

Re: Clean .bak files from PROC EXPORT


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

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) ;
New Contributor LRH
New Contributor
Posts: 2

Re: Clean .bak files from PROC EXPORT

Amazing! Thank you.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 3467 views
  • 8 likes
  • 9 in conversation