topic Re: NOTE: In a call to the FDELETE routine, the fileref ' ' exceeds 8 characters and will be truncat in SAS Programming

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

kmardinian — Tue, 23 Jul 2019 20:57:55 GMT

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

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

kmardinian — Tue, 23 Jul 2019 21:05:15 GMT

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

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

SASKiwi — Tue, 23 Jul 2019 21:14:39 GMT

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.

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

Kurt_Bremser — Tue, 23 Jul 2019 21:16:28 GMT

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

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

kmardinian — Tue, 23 Jul 2019 21:42:22 GMT

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

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

VDD — Wed, 24 Jul 2019 00:02:36 GMT

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=;

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

ScottBass — Wed, 24 Jul 2019 00:19:50 GMT

(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-the-file/td-p/575446.

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.

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

kmardinian — Wed, 24 Jul 2019 00:24:43 GMT

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

Thank you!

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

ScottBass — Wed, 24 Jul 2019 00:45:14 GMT

@ScottBass wrote:

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.

I've found the answer:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000210817.htm

If you look very closely at the example (which doesn't make clear that &fref isn't an already existing macro variable):

%if %sysfunc(fexist(&fref)) %then
      %put The file identified by the fileref
         &fref exists.;
   %else
      %put %sysfunc(sysmsg());

Changing this to open code:

%let rc=%sysfunc(filename(foo,G:\SAS\Work\_TD7752_MYSERVERNAME_\Prc2\class01.csv));
%put &=rc;
%let rc=%sysfunc(fexist(foo));  * fails ;
%put &=rc;
%let rc=%sysfunc(fexist(&foo));  * works ;
%put &=rc;

So, macro variable &foo does not exist, yes that's the syntax required for the fexist function (when called via %sysfunc)???

What genius came up with this syntax? Or am I missing something?

If you make this change to my previous code, the "all macro" version of %code works and deletes the CSV files:

* 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;
%loop_control(control=dirlist)

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000210817.htm (fexist)

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000245893.htm (fdelete - no macro example in the doc. fdelete also needs the bogus ampersand.)

I checked to see if the 9.4 documentation was updated:

http://support.sas.com/documentation/cdl/en/hostwin/69955/HTML/default/viewer.htm#n058nguffapu6sn11ujbnco8ohrf.htm

The "example" makes no sense.

http://support.sas.com/documentation/cdl/en/hostwin/69955/HTML/default/viewer.htm#p029yvwyao7xspn19xzxo3vyb0hz.htm

No examples at all. Seems like the documentation is regressing!

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

Tom — Wed, 24 Jul 2019 01:07:18 GMT

Huh?

The FEXIST() function takes as input the FILEREF of the file to be tested. You can just type the fileref or put the fileref into a macro variable and expand the macro variable.

If you try to reference a macro variable that doesn't exist then you will pass the FEXIST() a string with an & in it, which is an impossible value for a FILEREF.

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

ScottBass — Wed, 24 Jul 2019 01:41:02 GMT

@Tom wrote:

Huh?

The FEXIST() function takes as input the FILEREF of the file to be tested. You can just type the fileref or put the fileref into a macro variable and expand the macro variable.

If you try to reference a macro variable that doesn't exist then you will pass the FEXIST() a string with an & in it, which is an impossible value for a FILEREF.

Hi @Tom,

Yeah, I said "Huh?" myself, but not for the same reason as you.

Run the code I posted. It's self-contained, you can just paste it into SAS.

Edit: Actually, not really, you'll need some macros from https://github.com/scottbass/SAS/tree/master/Macro.

The fileref I used was 8 underscores, which more than likely won't clobber an existing fileref.

The code only works properly when I specify &________ for fexist and fdelete. That macro variable does not exist.

BTW, SAS 9.3 on Windows.

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

Tom — Wed, 24 Jul 2019 01:39:58 GMT

Perhaps the issue is somewhere else in the code?

This works fine with a fileref of 8 underscores.

%let fileref=________;
filename &fileref temp;

%put Does &fileref exist?  %sysfunc(fexist(&fileref)) ;
%put Does ________ exist? %sysfunc(fexist(________)) ;

data _null_;
  file ________;
  put 'Hello';
run;

%put Does &fileref exist?  %sysfunc(fexist(&fileref)) ;
%put Does ________ exist? %sysfunc(fexist(________)) ;

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

Tom — Wed, 24 Jul 2019 01:51:30 GMT

The FILENAME() function is probably your issue.

fileref

specifies the fileref to assign to the external file. In a DATA step, fileref can be a character expression, a string enclosed in single quotation marks that specifies the fileref, or a DATA step variable whose value contains the fileref. In a macro (for example, in the %SYSFUNC function), fileref is the name of a macro variable (without an ampersand) whose value contains the fileref to assign to the external file. If the function is used within a DATA step, the fileref must be enclosed in single quotation marks. If the function is used in macro code, the fileref must not be enclosed in quotation marks.

If you want to use the FILENAME() function to create filref of FRED the set a macro variable to the value FRED and pass the NAME of the macro to FILENAME() function.

%let fileref=________;
%let rc1=%sysfunc(filename(fileref,"my physical file"));
%let rc2=%sysfunc(fexist(&fileref));

Or leave the macro variable empty and the FILENAME() function will make up a fileref name for you.

704   %let fileref=;
705   %let rc=%sysfunc(filename(fileref,"C:\download\does no exist"));
706   %put &=fileref Does it Exist? %sysfunc(fexist(&fileref)) ;
FILEREF=#LN00196 Does it Exist? 0

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

ScottBass — Wed, 24 Jul 2019 01:52:20 GMT

* create file ;
filename foo "%sysfunc(pathname(work))\foo.csv";
data _null_;
   file foo;
   put 'blah blah blah';;
run;
filename foo;

* delete file using macro code ;
%let rc=%sysfunc(filename(________,%sysfunc(pathname(work))\foo.csv)); * works ;
%put &=rc;

%let exists=%sysfunc(fexist(________)); * fails, returns 0 not 1 ;
%put &=exists;
%let rc=%sysfunc(fdelete(________));  * fails, does not delete file ; ;
%put &=rc;

%let exists=%sysfunc(fexist(&________)); * works, returns 1, even though macro variable &foo does not exist ;
%put &=exists;
%let rc=%sysfunc(fdelete(&________));  * works, deletes file ;
%put &=rc;

%let rc=%sysfunc(filename(________));
%put &=rc;

And my SAS log:

27         * create file ;
28         filename foo "%sysfunc(pathname(work))\foo.csv";
29         data _null_;
30            file foo;
31            put 'blah blah blah';;
32         run;

NOTE: The file FOO is:
      Filename=G:\SAS\Work\_TD8076_XXXXXXXX_\Prc2\foo.csv,
      RECFM=V,LRECL=256,File Size (bytes)=0,
      Last Modified=24 July 2019 11:49:24,
      Create Time=24 July 2019 11:49:24

NOTE: 1 record was written to the file FOO.
      The minimum record length was 14.
      The maximum record length was 14.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

33         filename foo;
NOTE: Fileref FOO has been deassigned.
34         
35         * delete file using macro code ;
36         %let rc=%sysfunc(filename(________,%sysfunc(pathname(work))\foo.csv)); * works ;
37         %put &=rc;
RC=0
38         
39         %let exists=%sysfunc(fexist(________)); * fails, returns 0 not 1 ;
40         %put &=exists;
EXISTS=0
41         %let rc=%sysfunc(fdelete(________));  * fails, does not delete file ; ;
42         %put &=rc;
RC=20004
43         
44         %let exists=%sysfunc(fexist(&________)); * works, returns 1, even though macro variable &________ does not exist ;
45         %put &=exists;
EXISTS=1
46         %let rc=%sysfunc(fdelete(&________));  * works, deletes file, even though macro variable &________ does not exist ;
47         %put &=rc;
RC=0
48         
49         %let rc=%sysfunc(filename(________));
50         %put &=rc;
RC=0

I admit it's weird, and perhaps I'm missing something, but I don't get it...

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

Tom — Wed, 24 Jul 2019 01:53:25 GMT

You told FILENAME() to use the MACRO VARIABLE named ________.

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

ScottBass — Wed, 24 Jul 2019 02:02:01 GMT

Thanks.

I still contend that SAS R&D could have made the syntax better when invoking filename/fexist/fdelete via %sysfunc.

Why do I need to create a macro variable for the fileref parameter to filename, instead of just entering the text that the macro variable would contain (like everywhere else in SAS)?

Perhaps if I don't specify a macro variable as the parameter to the filename function(contrary to the documentation, as you pointed out), the filename function creates &________?

I still contend poor syntax and poor documentation, but thanks for clarifying.

Edit: Yeah, see here. Again, the doc could be clearer:

* create file ;
filename foo "%sysfunc(pathname(work))\foo.csv";
data _null_;
   file foo;
   put 'blah blah blah';;
run;
filename foo;

* delete file using macro code ;
%let rc=%sysfunc(filename(________,%sysfunc(pathname(work))\foo.csv)); * works ;
%put &=rc;
%put &=________;  * <<<<<<<<<< ;

%let exists=%sysfunc(fexist(________)); * fails, returns 0 not 1 ;
%put &=exists;
%let rc=%sysfunc(fdelete(________));  * fails, does not delete file ; ;
%put &=rc;

%let exists=%sysfunc(fexist(&________)); * works, returns 1, even though macro variable &foo does not exist ;
%put &=exists;
%let rc=%sysfunc(fdelete(&________));  * works, deletes file ;
%put &=rc;

%let rc=%sysfunc(filename(________));
%put &=rc;

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

ScottBass — Wed, 24 Jul 2019 02:04:48 GMT

@Tom wrote:

You told FILENAME() to use the MACRO VARIABLE named ________.

Which did not exist (I started a fresh SAS session), yet "worked" (no unresolved macro variable warning), THEN created &________ (AFAIK undocumented behaviour).

Anyway, I didn't mean to hijack @kmardinian post. PM me to discuss further 😉

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

ChrisNZ — Wed, 24 Jul 2019 02:16:33 GMT

In a macro (for example, in the %SYSFUNC function), fileref is the name of a macro variable (without an ampersand) whose value contains the fileref to assign to the external file.

I think that's why @ScottBass got his knickers in a twist.

There are very few cases where one can access the value of a macro variable without using ampersand. I can think of symget() and %superq().

Why the filename() function was implemented this way seems atypical indeed.

It's probably so that "discardable" names such as #LN00196 can be created transparently, in a manner similar to the TEMP device, as shown in the common examples of this function being used in the macro language:

%let fileref=;
%let rc=%sysfunc(filename(fileref,&path));

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

kmardinian — Wed, 24 Jul 2019 03:25:00 GMT

%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.

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

ChrisNZ — Wed, 24 Jul 2019 05:40:38 GMT

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;