DATA Step, Macro, Functions and more

Delete XLS file

Reply
Regular Contributor
Posts: 181

Delete XLS file

I am using EG5.1. Is there a way we can delete excel file that is already saved on a server?

I am using the code below. I want to delete XLS if it exists already in this path - /fts/sas/bhandari/.

ODS HTML FILE = "/fts/sas/bhandari/results.xls";

PROC PRINT DATA = A;

RUN;

PROC PRINT DATA = A;

RUN;

ODS HTML CLOSE;

Is there a better way to output the result in a single data set or excel file? I have 2-3 tables. I want to stack the results. Each output is structured differently. I cannot use SET statement to combine them.

Thanks in anticipation!

Super User
Super User
Posts: 6,502

Re: Delete XLS file

SAS now has an FDELETE() function that can delete a file.

Why not just PROC EXPORT to create an XLS file? 

Or ODS TAGSETS.EXCELXP it you really want to make a single sheet that two independent tables.

Super User
Posts: 6,946

Re: Delete XLS file

You are creating HTML content and write it to a file with a .xls extension. VERY bad style.

Use .html as the extension!

If you want to create something that will show well in Excel, including formatting and using multiple sheets, or multiple reports within one sheet, look at ODS TAGSETS.EXCELXP. Use .xml as the extension for the output file, as TAGSETS.EXCELXP creates XML code, and Excel may complain when opening a file where the content does not correspond to the filename extension.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,407

Re: Delete XLS file

Well, do you have permissions to read/write to that area?  Is there a reason why you need to delete the file?  As mentioned by JurtBremser, tagsets.excelxp will produce nicer looking XML files which Excel can read, however, what is the reason behind using Excel?  Other than a specific request for something in Excel, and then more fool the person asking, I would never choose that as an output format for any purpose, its not a good transfer format, and review wise its to open to abuse.

SAS Super FREQ
Posts: 683

Re: Delete XLS file

Hi

Find below a code example where the FDELETE function is used in a macro

%macro fdel(file);
 
%local fref;
  %let fref = __fdel;

 
%if %sysfunc( fileexist(&file) ) = 0 %then %do;
   
%put WARNING: &sysmacroname &file does not exist;
    %return;
 
%end;
 
%let rc= %sysfunc( filename(fref,&file) );
  %let rc= %sysfunc( fdelete(&fref) );
  %let rc= %sysfunc( filename(fref, ) );
%mend;

%let delfile = /tmp/deleteTest.txt;

data _null_;
 
file "&delfile";
 
put "test";
run;

%
fdel(&delfile)


Bruno

Frequent Contributor
Posts: 127

Re: Delete XLS file

Hi,

 

Thank you. This is helpful, in my environment I was not allowed to use xcommands and found this as my solution for a way how to delete files without using Xcommands.

Thanks,
Suryakiran
Ask a Question
Discussion stats
  • 5 replies
  • 532 views
  • 1 like
  • 6 in conversation