topic Re: Removing the .bak with proc export in SAS Programming

Removing the .bak with proc export

Gieorgie — Thu, 23 Dec 2021 10:11:14 GMT

I have a problem because when exporting with proc export a few excels creates an additional file with the abbreviation .bak. I searched the form and found such a macro, but it does not work for me. Maybe I am doing something wrong, or is there any possibility to change the settings that did not back up with proc export

proc export data=DIFF_FINAL
            outfile="&glb_path2./reports/mth_ak/zab_polisy&gv_tbl_date..xlsx"
            dbms=xlsx
            replace;
            sheet="Roznice";
run; 

%macro xlsx_bak_delete(file=&glb_path2./reports/mth_ak/zab_polisy&gv_tbl_date) / 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;

Re: Removing the .bak with proc export

maguiremq — Thu, 23 Dec 2021 10:59:14 GMT

This is a bit outside my usual work/knowledge in SAS, but I have a couple questions.

1. Have you considered using the XLSX engine in the LIBNAME statement instead of PROC EXPORT? (link)

2. Can you show us your log with the messages? The macro turns uses MPRINT, and seeing what's going on would help. You might even want to turn on SYMBOLGEN to see if your macro variables are resolving correctly.

3. Are you having to delete multiple .bak files? If not, There may not be a need for a macro.

4. It seems as though you got the macro from the following link. Did you see the comment that someone uses the EXCEL engine and doesn't have any issues? I'm taking their word for it, but I don't typically use Excel files. (link)

Also, note that in the blog post in (1) that the XLSX engine directly accesses the excel file, whereas the EXCEL engine relies on Microsoft API's.

Re: Removing the .bak with proc export

Gieorgie — Thu, 23 Dec 2021 11:05:16 GMT

Hi its resolved now :

data _null_;
	fname = "todelete";
	rc = filename(fname, "&glb_path2./reports/mth_ak/zab_polisy&gv_tbl_date..xlsx.bak");
	if rc = 0 and fexist(fname) then rc=fdelete(fname);
	rc = filename(fname);
run;

Re: Removing the .bak with proc export

Kurt_Bremser — Thu, 23 Dec 2021 11:13:32 GMT

We would need to see how you call the macro (if you did not call it, then nothing would happen anyway).

Re: Removing the .bak with proc export

Tom — Thu, 23 Dec 2021 16:20:23 GMT

The code you posted does not include any call to the macro. So you never asked it to delete the .bak file.

%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;
%xlsx_bak_delete(file=&glb_path2./reports/mth_ak/zab_polisy&gv_tbl_date)

Re: Removing the .bak with proc export

klchtsk — Fri, 02 Dec 2022 16:13:07 GMT

If you run SAS on Linux environment you can call Linux commands.
Just add this to the end of the code.
x cd /path_to_folder;
 x rm *.xlsx.bak;

Re: Removing the .bak with proc export

tinton — Mon, 17 Apr 2023 04:21:56 GMT

It's working for me! Thanks for this code