BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Gieorgie
Quartz | Level 8

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;

Gieorgie_0-1640254260177.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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)

View solution in original post

6 REPLIES 6
maguiremq
SAS Super FREQ

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.

Gieorgie
Quartz | Level 8

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;
tinton
Fluorite | Level 6
It's working for me! Thanks for this code
Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 6822 views
  • 2 likes
  • 6 in conversation