Check if the file exists inside of a macro

[DrBoogie]

Hi all, I am trying now to set together several files, with this code:

%macro imp;
%let mon = jan feb mar apr mai jun jul aug sep okt nov dez;
data outtab;
set 
%do jahr=16 %to 18;
  %do i=1 %to %sysfunc(countw(&mon));
   %let next_mon = %scan(&mon, &i);
     work.to_&next_mon._&jahr.   
  %end;
%end;
;
run;
%mend;

It works fine. But it could be potentially a problem if one of the files doesn't exist. I want now to incorporate a corresponding check. So I made this change:

%macro imp;
%let mon = jan feb mar apr mai jun jul aug sep okt nov dez;
data outtab;
set 
%do jahr=16 %to 18;
  %do i=1 %to %sysfunc(countw(&mon));
   %let next_mon = %scan(&mon, &i);
     %if fileexist(to_&next_mon._&jahr.) %then work.to_&next_mon._&jahr.;   
  %end;
%end;
;
run;
%mend;

Unfortunately this doesn't work because %if fileexist is always FALSE although the files are available. I guess that %if fileexist cannot be placed in the data step. But what is the alternative way? Do You have some idea?

Thanks in advance.

[PaigeMiller]

Please see the FILEEXIST documentation

http://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=lefunctionsref&docsetTarget=n0...

They give an example of using FILEEXIST inside of a macro involving the use of macro function %SYSFUNC

[Astounding]

For macro language to use FILEEXIST, try wrapping it up inside %SYSFUNC:

%if %sysfunc(fileexist(to_&next_mon._&jahr.)) %then work.to_&next_mon._&jahr.;   

[DrBoogie]

Then I get

ERROR: Required operator not found in expression: fileexist(work.to_&next_mon._&jahr.)

[Tom]

---

@DrBoogie wrote:

Then I get

ERROR: Required operator not found in expression: fileexist(work.to_&next_mon._&jahr.)

---

FILEEXIST() is for physical  filenames. 

If you want to test if a dataset exists using a one or two level name then use the EXIST() function instead.

[DrBoogie]

Thanks a lot!

[RW9]

Make you life a lots easier (well less easy than not having like data in many different datasets in the first place) by having all your datasets either in one naming type, or in one libname.  For instance one way using code generation:

data _null_;
  set sashelp.vtable (where=(libname="TMP")) end=last;
  if _n_=1 then call execute('data want; set'); 
  call execute(' tmp.'||strip(memname));
  if last then call execute('; run;');
run;

This will set every dataset in library tmp together - i.e. you don't need to do any calculation or checking the file exists.  You can also use lists:

data want;
  set tmp.jan_: tmp.feb_:  ...;
run;

Note the colon after the prefix, means everything with a prefix jan_ for example.

Various methods of doing such a task, but should never really be needed as storing data out into small blocks with data (dates in this case) is no really a good storage method, very "Excel thinking".

[DrBoogie]

Thank You, it's  a nice idea, but then I have to be sure that there are no other files in the library which is also not an optimal way for me.

[RW9]

Yes, unfortunately with a bad storage solution, you will end up with bad processes.  Another way:

- set all data together, using indsname to create a date variable for filtering

- filter this dataset for the date ranges you want

[DrBoogie]

"Yes, unfortunately with a bad storage solution, you will end up with bad processes"

Yep. but I have no influence at all as for the storage solution. I am a very small person in a very large company. 🙂

[data_null__ · Accepted solution]

For SAS data set you need to use EXIST function.

%sysfunc(exist(one-or-two-level-name))