List of files in directory is zero

[Citrine10]

hi, I have a macro that outputs the list of files in a directory. 

Thereafter I do a count so that  I can save this number of files in a variable. My issue comes in when there are zero files in the directory, how do I handle this as this zero is not saved in the variable 

%list_files("C:/Files",txt)

/*The above macros shows the results in a dataset called tables*/

/*Count number of files*/
proc sql;
create table filecount as
select count(name) into: filecount 
from tables;
quit;

%if &filecount. = 0 %then %goto section1;  /*This is where it breaks as the 0 is not stored in the &filecount variable*/

[PaigeMiller]

There are many solutions. Here's one:

%let filecount=0;

%list_files("C:/Files",txt)

/*The above macros shows the results in a dataset called tables*/

/*Count number of files*/
proc sql;
create table filecount as
select count(name) into: filecount 
from tables;
quit;

%if &filecount. = 0 %then %goto section1;  /*This is where it breaks as the 0 is not stored in the &filecount variable*/

By setting &filecount to zero before the macro %list_files, it will always have a value, and if there are no files found by %list_files, then &filecount=0

[Citrine10]

thank you
But then if there are 0 files in the dir it gives me the error :ERROR: File WORK.TABLES.DATA does not exist.

Thats because no records are found so this table doesn't exist. Is there any other way to do this?

What if the macro automatically stores the count in a variable so that we do not have to do the select statement? I'm not sure if that is possible

[PaigeMiller]

In this case, the easiest thing to do is to ignore that error, as it is telling you that there are no files in the folder so %list_files doesn't return a value, but the rest of your macro code still works, the macro variable is still zero and that's what you want.

What if the macro automatically stores the count in a variable so that we do not have to do the select statement?

I don't know what this means.

[Kurt_Bremser]

You would need to change the macro so that it creates the dataset TABLES regardless of the number of files found. Since we do not know the macro code (yet), we can't assist you there.

One workaround:

%list_files("C:/Files",txt)

%let filecount = 0;

%if %sysfunc(exist(tables))
%then %do;
/*Count number of files*/
proc sql;
create table filecount as
select count(name) into: filecount 
from tables;
quit;
%end;

The SQL will only be attempted if the dataset exists; since the setting of the default value comes before the conditional block, you will always get the zero if everything else fails.

[Tom]

You have to decide if you want PROC SQL to make a dataset or make macro variables.  You cannot have it do both at the same time.  You also should code against the possibility that the query will not generate anything.

proc sql noprint;
%let filecount=0;
select count(name)
  into :filecount trimmed
  from tables
;
quit;