NOTE: In a call to the FDELETE routine, the fileref ' ' exceeds 8 characters and will be truncated

[kmardinian]

I am trying to delete files from a folder by using the FDELETE statement. But I keep getting this note that my file exceeds 8 characters and will be truncated and then they are not deleted.

%macro loop;
%if &n %then
%do;
%do i=1 %to &n;
%let filein=%scan(&filename,&i,'|');
%PUT filein=&filein;

data _null_;
fname="&filein";
rc=filename(fname,"&path1");
if rc = 0 and fileexist(fname) then
rc=fdelete(fname);
rc=filename(fname);
run;

%end;

%end;
%mend;

%loop;

LOG:

filein=SUBJECT213453.csv

NOTE: In a call to the FDELETE routine, the fileref C:\Users\K.M\Desktop\SUBJECT213453.csv exceeds 8
characters, and will be truncated.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

[kmardinian]

Sorry the path1 should be this instead C:\Users\K.M\Desktop\Reads\SUBJECT213453.csv;

But even with this fixed, I am still getting the same NOTE

[SASKiwi]

FNAME is an alias of no more than 8 characters that points to the the actual file name, not the actual file name itself as you have assigned it.

[Kurt_Bremser]

You are using a physical filename in a place where you need a valid SAS name of maximal 8 characters.

Try this:

fname="fileref";
rc=filename(fname,"&path1./&filein.");
if rc = 0 and fileexist("&path./&filein.") then

[kmardinian]

Hi Kurt, Thank you, that almost worked perfectly. But instead of just deleting the files, it also deleted the entire folder. In addition, for some reason, my macro didn't stop after reaching the last file and kept going...

%macro loop;
%if &n %then
%do;
%do i=1 %to &n;
%let filein=%scan(&filename,&i,'|');
%PUT filein=&filein;

data _null_;
fname="fileref";
rc=filename(fname,"&path1.&filein.");
if rc = 0 and fileexist("&path1./&filein.") then
rc=fdelete(fname);
rc=filename(fname);
run;

%end;

%end;
%mend;

%loop;

filein=SUBJECT9202.csv

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

filein=SUBJECT7829.csv

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

filein=

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

filein=

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

[ChrisNZ · Accepted solution]

1. Read the documentation about the functions you use

2. You don't need a macro at all:

%* PREPARE DATA; 
data _null_;
  file 'c:\temp\b.csv'; 
  put 'a'; 
run;

%let n        = 2;
%let filenames= a.csv | b.csv;
%let path1    = c:\temp\ ;

%* DELETE FILES;
data _null_;
  do I=1 to &n;
    FILENAME=cats("&path1",scan("&filenames",I,'|'));  
    if ^fileexist(FILENAME) then continue;
    RC=filename("StudyID",FILENAME);
    if RC=0 then RC=fdelete("StudyID");
  end;
  RC=filename("StudyID");
run;

[kmardinian]

Hi Chris, I was using the macro to loop through all the csv files, but I gave your way a shot. I didn't get any errors, but the files were still not deleted from the folder.

 data _null_;
45           do I=1 to &n;
46             FILENAME=cats("&path1",scan("&filename",I,'|'));
47             if ^fileexist(FILENAME) then continue;
2                                                          The SAS System                               22:48 Tuesday, July 23, 2019

48             RC=filename("StudyID",FILENAME);
49             if RC=0 then RC=fdelete("StudyID");
50           end;
51           RC=filename("StudyID");
52         run;
53         
54         			%end;
55         
56         		%end;
57         %mend;
58         
59         %loop;
MLOGIC(LOOP):  Beginning execution.
MLOGIC(LOOP):  %IF condition &n is TRUE
MLOGIC(LOOP):  %DO loop beginning; index variable I; start value is 1; stop value is 6; by value is 1.  
MLOGIC(LOOP):  %LET (variable name is FILEIN)
MLOGIC(LOOP):  %PUT filein=&filein
filein=SUBJECT2930.csv

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

MLOGIC(LOOP):  %DO loop index variable I is now 2; loop will iterate again.
MLOGIC(LOOP):  %LET (variable name is FILEIN)
MLOGIC(LOOP):  %PUT filein=&filein
filein=SUBJECT6782.csv

[ChrisNZ]

Why is there macro leftovers in your log?

[kmardinian]

Hi Chris, I ended up just updating my path to a different folder other than the desktop and it worked! I am not sure why that is though...

[ScottBass]

2. You don't need a macro at all:

MLOGIC(LOOP):  Beginning execution.

What's wrong with this picture?

Get your code to work in a data step first, i.e. modify Chris' example for your environment.  Then, and only then, macroize your working data step code.

Also, if you're using DMS or EG, it might be useful for you to hit the documentation (or grab some SAS Global Forum papers) on the data step debugger.

[ChrisNZ]

@ScottBass 

>Then, and only then, macroize your working data step code.

No need, the data step does all the processing shown.

>Also, if you're using DMS or EG, it might be useful for you to hit the documentation on the data step debugger.

That's one of EG's many frustrating limitations that the DS debugger cannot be used, isn't it? 

Oh my bad. There is such a thing is a more recent version than the one I have to suffer.

[kmardinian]

%macro loop;
	%if &n %then
		%do;
			%do i=1 %to &n;
				%let filein=%scan(&filename,&i,'|');
				%PUT filein=&filein;

data test;
fname = "StudyID";
rc=filename(fname,"&path1.&filein");
if rc = 0 and fileexist("path1./&filein.") then
rc=fdelete(fname);
rc=filename(fname);
run;


			%end;

		%end;
%mend;

%loop;

Hi Kurt, when you say a valid SAS name, is this a SAS variable that is located in the csv files or just any 8 character name?

This is what I have so far, but it still is not deleting the files in the appropriate folder.

[VDD]

why not just do something like this

proc sql noprint ;
select 'drop table '||memname into :empties separated by ';'
from dictionary.tables
where libname='WORK' and nobs=0
;
&empties;
quit;

%let empties=;

[kmardinian]

Hi, I am trying to understand how your proc sql statement works and is calling my files?

Thank you!

[ScottBass]

(Can you post your code using the Insert SAS Code icon???)

So this would be related to https://communities.sas.com/t5/SAS-Programming/Pulling-files-from-an-output-folder-without-knowing-t....

Here is some sample code:

* create 10 CSV files ***having the same format*** ;
%let pathname=%sysfunc(pathname(work));
%macro code;
   %do i=1 %to 10;
      %let i=%sysfunc(putn(&i,z2.));
      proc export data=sashelp.class dbms=csv outfile="&pathname\class&i..csv";
      run;
   %end;
%mend;
%code;

* check created file ;
data _null_;
   infile "&pathname\class01.csv";
   input;
   put _infile_;
run;

* build a directory list ;
%dirlist(dir=&pathname, filter=ext='csv')

* check dirlist ;
proc print data=dirlist;
run;

* read each file ;
%macro code;
%let fullname=%sysfunc(strip(&fullname));
data WORK.TEMP;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile "&fullname" delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2;
   informat Name $7. ;
   informat Sex $1. ;
   informat Age best32. ;
   informat Height best32. ;
   informat Weight best32. ;
   format Name $7. ;
   format Sex $1. ;
   format Age best12. ;
   format Height best12. ;
   format Weight best12. ;
input
            Name $
            Sex $
            Age
            Height
            Weight
;
if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
run;

proc append data=work.temp base=work.want;
run;
%mend;

* delete base table from previous runs ;
%kill(data=work.want)

* call code macro for every row in dirlist ;
%loop_control(control=dirlist)

* check final table ;
proc print data=want;
run;

* delete the CSVs ;
%macro code;
   %let fullname=%sysfunc(strip(&fullname));
   %let rc=%sysfunc(filename(________,&fullname));
   %if (&rc eq 0 and %sysfunc(fexist(________)) eq 1) %then %do;
      %let rc=%sysfunc(fdelete(________));
      %if (&rc eq 0) %then 
         %put &fullname deleted.;
      %else
         %put &fullname was not deleted.;
   %end;
   %let rc=%sysfunc(filename(________));
%mend;

%macro code;
   %let fullname=%sysfunc(strip(&fullname));
   data _null_;
      rc=filename("________","&fullname");
      if (rc eq 0 and fexist("________")) then do;
         rc=fdelete("________");
         if (rc eq 0) then
            putlog "&fullname was deleted.";
         else
            putlog "&fullname was not deleted.";
      end;
      rc=filename("________");
   run;
%mend;
%loop_control(control=dirlist)

* check the results (should be empty dataset) ;
%dirlist(dir=&pathname, filter=ext='csv')

However, now I'll hijack your post 😉  (temporarily)...

I often like a "pure macro" solution when possible (or if it makes sense).  So, does anyone know why the first delete files macro fails?  I know it's due to the fexist function, I just don't see why it's returning 0 instead of 1?  The second data step macro works, and does delete the CSV files.