topic Re: how to delete all files in data step using fdelete function in SAS Programming

how to delete all files in data step using fdelete function

knveraraju91 — Mon, 15 Apr 2019 19:39:12 GMT

FILENAME spec "%sysfunc(pathname(fptotb,f))/have.rtf"  ;

  DATA _NULL_ ;
  rc = FDELETE('spec') ;
  RUN ;

Hi In my folder, there are rft, lst, log, 7bdat are created with same name during a data step which i need to delete. for example, the temp file are have.rtf, have.log, have.lst, have.7bdat. With above pgm I am able to delete only rtf. How to remove all in step. Please suggest. Thank you

Re: how to delete all files in data step using fdelete function

yabwon — Mon, 15 Apr 2019 20:05:49 GMT

Hi,

maybe if you do know files extensions in advance you can loop:

data _null_;
  fname="tempfile";
  length ext $ 20;
  do ext = "rtf", "lst", "log", "sas7bdat", "txt";

    rc=filename(fname, "%sysfunc(pathname(work))/have." !! strip(ext));
    if rc = 0 and fexist(fname) then
       rc=fdelete(fname);
    rc=filename(fname);
  end;
run;

all the best

Bart

Re: how to delete all files in data step using fdelete function

Reeza — Mon, 15 Apr 2019 21:02:08 GMT

Do you have x commands enabled? In this case, if you do, a system command will accept wildcards which would help.

Re: how to delete all files in data step using fdelete function

ChrisNZ — Tue, 16 Apr 2019 02:55:47 GMT

If you must use function fdelete(), then this works:

%* create files;
data _null_; 
 file "%sysfunc(pathname(work))/have.txt" ; put 'a';
 file "%sysfunc(pathname(work))/have.csv" ; put 'a';
run;

filename SPEC "%sysfunc(pathname(work))/have.*" ;

%* List files;
data FILENAMES;
  length _FILENAME $ 256; 
  infile SPEC filename=_FILENAME;
  input ; 
  FILENAME = _FILENAME;
run;
% Delete files;
data _null_; 
  set FILENAMES;  
  rc=filename ('TMPFREF',FILENAME); 
  rc=fdelete  ('TMPFREF') ;          
  rc=filename ('TMPFREF');           
run;

Re: how to delete all files in data step using fdelete function

ChrisNZ — Tue, 16 Apr 2019 03:02:40 GMT

If you needn't use function fdelete(), then this is easier:

data _null_; 
 file "%sysfunc(pathname(work))/have.txt" ; put 'a';
 file "%sysfunc(pathname(work))/have.csv" ; put 'a';
run;

filename DEL pipe "del ""%sysfunc(pathname(work))\have.*"" " ;

data FILENAMES;
  infile DEL ;
  input ; 
  put _infile_;
run;

Any error messages generated during deletion go to the log window here, feel free to keep them in a table instead.

Re: how to delete all files in data step using fdelete function

Ksharp — Tue, 16 Apr 2019 13:11:53 GMT

You can get the all the file name by the following code ,and write CALL EXECUTE() to delete them one by one.


  DATA _NULL_ ;
  rc = filename('spec','c:\temp') ;
  did=dopen('spec');
  do i=1 to dnum(did);
   file_name=dread(did,i);
   put file_name= ;
  end;
  RUN ;

Re: how to delete all files in data step using fdelete function

elwayfan446 — Tue, 22 Dec 2020 16:30:56 GMT

@Ksharp , I stumbled upon this post looking for an alternate way to delete files from my directory on a server than using the x command. Normally, if I use an x command with a wildcard to delete *.xlsx files it works, however, we are running into issues where it won't work for some reason.

Reading your reply, I got this syntax to work to generate a list of my files in that directory. Can you advise on how I would use the CALL EXECUTE() to delete the files now?

DATA _NULL_ ;
  rc = filename('spec','c:\temp') ;
  did=dopen('spec');
  do i=1 to dnum(did);
   file_name=dread(did,i);
   put file_name= ;
  end;
  RUN ;

Re: how to delete all files in data step using fdelete function

Reeza — Tue, 22 Dec 2020 16:43:49 GMT

Wrap that in a macro function and call it via CALL EXECUTE the same way I wrote that last program for you.

Re: how to delete all files in data step using fdelete function

elwayfan446 — Tue, 22 Dec 2020 17:00:32 GMT

What I don't understand is how the files actually get deleted. Where & how in that code do I tell it to delete each of those files?

That last program to help me cat the variables in the file name was great. Thank you for that.

Re: how to delete all files in data step using fdelete function

Reeza — Tue, 22 Dec 2020 17:18:10 GMT

Add the FDELETE function into the loop using the fileref definition or path. file_name gave you the filename and you have the path so you can create the full path if needed.

Re: how to delete all files in data step using fdelete function

elwayfan446 — Tue, 22 Dec 2020 18:39:10 GMT

I have tried a few ways and can't get it to work correctly.

I could only get the directory name working if I created a variable. I think that's probably an extra step but couldn't get it to work otherwise. If I run this code with the put fname uncommented, I see the complete path and file name in the SAS log.

%let directoryname = "/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/";

DATA _NULL_ ;
  rc = filename('spec',"/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/") ;
  did=dopen('spec');
  do i=1 to dnum(did);
   file_name=dread(did,i);
   /*put file_name= ;*/
   fname=cats(&directoryname,file_name);
   put fname;
   /*rc=fdelete(fname);*/
  end;
RUN ;

I am thinking, "OK, these are the files I need deleted, let's go".

I run the code with rc=fdelete(fname);

DATA _NULL_ ;
  rc = filename('spec',"/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/") ;
  did=dopen('spec');
  do i=1 to dnum(did);
   file_name=dread(did,i);
   /*put file_name= ;*/
   fname=cats(&directoryname,file_name);
   /*put fname;*/
   rc=fdelete(fname);
  end;
RUN ;

I now get the following notes in the log and the files have not been deleted:

I have been referencing this documentation from SAS as well and just can't seem to put it all together.

https://documentation.sas.com/?cdcId=vdmmlcdc&cdcVersion=8.1&docsetId=lefunctionsref&docsetTarget=p0h945u5r0cv6yn1u6qs35hiqt9t.htm&locale=en

Re: how to delete all files in data step using fdelete function

Reeza — Tue, 22 Dec 2020 20:36:45 GMT

It seems to want the fileref not the filename. You can create a fileref using the FILENAME function, similar to the first line of code.

Re: how to delete all files in data step using fdelete function

elwayfan446 — Tue, 22 Dec 2020 21:23:35 GMT

I tried this according to the SAS documentation I found. I just can't put it all together.

%let directoryname = "/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/";

DATA _NULL_ ;
  rc = filename('spec',"/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/") ;
  did=dopen('spec');
  do i=1 to dnum(did);
    file_name=dread(did,i);
   /*put file_name= ;*/
   fname=cats(&directoryname,file_name);
   /*put fname;*/
   /*rc=fdelete(fname);*/
  end;
RUN ;


%macro test;                                                                                                                            
 %let filrf= "/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/";                                                                                                                     
 %let rc=%sysfunc(filename(filrf, file_name=));                                                                                  
 %if &rc ne 0 %then %put %sysfunc(sysmsg());                                                                                            
 %let rc=%sysfunc(filename(filrf));                                                                                                     
%mend test;

Re: how to delete all files in data step using fdelete function

Reeza — Tue, 22 Dec 2020 21:25:03 GMT

Get it working for a single folder/file first. Then figure out how to loop it.

Re: how to delete all files in data step using fdelete function

elwayfan446 — Tue, 22 Dec 2020 22:21:08 GMT

Sorry for all of the back and forth. I promise I am not someone who needs their homework answered, I have just been racking my brain on this one and the other regarding the excel export files for a week. I try not to post for help unless it is a last resort.

I am trying it with a single file in that directory. When I try what looks right (and probably isn't at this point), I can %put the filrf but when I %put rc it gives me what looks like a number in the log.

DATA _NULL_ ;
  %let filrf="/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/";	
  %let rc=%sysfunc(filename(filrf, 202011_360_Mortgage__LLC.xlsx));
  %put &filrf;
  %put &rc;
RUN ;

Re: how to delete all files in data step using fdelete function

elwayfan446 — Tue, 22 Dec 2020 23:31:54 GMT

I have it working for one file.

/* THIS WORKS ON ONE FILE */
DATA _NULL_ ;
  rc = filename('fname',"/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/202011_All_Western_Mortgage__Inc..xlsx");
  rc = fdelete('fname');	
RUN ;

I have tried to modify it to loop through all of the files in the directory with no luck.

DATA _NULL_ ;
  rc = filename('fpath',"/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/");
  	did=dopen('fpath');
  	do i=1 to dnum(did);
	  file_name=dread(did,i);
	  /*put file_name;*/
  		rc = filename('fname',catx('fpath',file_name));
		put fname;
  		/*rc = fdelete('fname');*/	
	end;
RUN ;

This block of code gives me these results when I put to the log:

I appreciate everyone wanting me to figure this one out on my own, however, I have spent several days on this and need to figure this out to complete a project for my team. If you could just show me and example on how I need to change my code I would greatly appreciate it.

Re: how to delete all files in data step using fdelete function

SASKiwi — Wed, 23 Dec 2020 01:03:51 GMT

File_name is the name of your file not FNAME - there is no FNAME variable. Try this, if RC = 0 - the FILENAME function is successful - then the DELETE is done.

DATA _NULL_ ;
  rc = filename('fpath',"/sae/aal_labapm/01/mortgage/msracq/DATA WAREHOUSE/DWACQ/REPORT FILES/MSR0028/");
  	did=dopen('fpath');
  	do i=1 to dnum(did);
	  file_name=dread(did,i);
	  /*put file_name;*/
  		rc = filename('fname',catx('fpath',file_name));
		put file_name = rc = ;
  		if rc = 0 then rc = fdelete('fname');	
	end;
RUN ;

Re: how to delete all files in data step using fdelete function

ChrisNZ — Wed, 23 Dec 2020 01:06:21 GMT

rc = filename('fname',catx('fpath',file_name));

is wrong. FPATH is not the path. 'FPATH' even less.

Also, function catx is used wrongly.

Maybe this?

RC = filename('FNAME',cats(pathname('FPATH'),FILE_NAME));

Use might need a slash separator.

Re: how to delete all files in data step using fdelete function

elwayfan446 — Wed, 23 Dec 2020 14:07:46 GMT

@SASKiwi This code ran successfully but the files were not deleted. Here is the log.

Files still out there:

I am starting to wonder if there is something wrong with the actual filepath or file names that are preventing these types of mass delete since x commands are only working on hard coded files too. So frustrating.

Re: how to delete all files in data step using fdelete function

elwayfan446 — Wed, 23 Dec 2020 14:11:06 GMT

Thansk @ChrisNZ. I tried this adjustment as well. Same results as @SASKiwi's script.