BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
felyp222
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
LaurieF
Barite | Level 11

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

22 REPLIES 22
Doug____
Pyrite | Level 9
Kurt have there been any updates on this issue? It is a bit of a pain in a version-controlled environment.
elenarojco
Calcite | Level 5

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;

 

Reeza
Super User

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. 

Doug____
Pyrite | Level 9
Any updates on this?
LaurieF
Barite | Level 11

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

Fely
Calcite | Level 5
Thank you so much!
This is what I was looking for

Feli
Iris22
Calcite | Level 5

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

 

DBMS=excel

njr
Calcite | Level 5 njr
Calcite | Level 5

This worked for me!

LRH
Calcite | Level 5 LRH
Calcite | Level 5

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 🙂

Tom
Super User Tom
Super User

@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) ;
LRH
Calcite | Level 5 LRH
Calcite | Level 5

Amazing! Thank you.

NormaO
Calcite | Level 5

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;

Reeza
Super User

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

SAS Innovate 2025: Register Now

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!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 22 replies
  • 44058 views
  • 28 likes
  • 15 in conversation