topic Re: Check if the file exists inside of a macro in SAS Programming

Check if the file exists inside of a macro

DrBoogie — Wed, 09 May 2018 12:45:01 GMT

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.

Re: Check if the file exists inside of a macro

PaigeMiller — Wed, 09 May 2018 12:48:33 GMT

Please see the FILEEXIST documentation

http://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=lefunctionsref&docsetTarget=n06xm8hwk0t0axn10gj16lfiri43.htm&locale=en

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

Re: Check if the file exists inside of a macro

Astounding — Wed, 09 May 2018 12:49:10 GMT

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

Re: Check if the file exists inside of a macro

RW9 — Wed, 09 May 2018 12:57:59 GMT

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

Re: Check if the file exists inside of a macro

DrBoogie — Wed, 09 May 2018 13:16:44 GMT

Then I get

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

Re: Check if the file exists inside of a macro

DrBoogie — Wed, 09 May 2018 13:23:37 GMT

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.

Re: Check if the file exists inside of a macro

RW9 — Wed, 09 May 2018 13:27:24 GMT

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

Re: Check if the file exists inside of a macro

DrBoogie — Wed, 09 May 2018 13:31:43 GMT

"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. 🙂

Re: Check if the file exists inside of a macro

Tom — Wed, 09 May 2018 14:53:36 GMT

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

Re: Check if the file exists inside of a macro

data_null__ — Wed, 09 May 2018 15:32:37 GMT

For SAS data set you need to use EXIST function.

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

Re: Check if the file exists inside of a macro

DrBoogie — Wed, 09 May 2018 20:31:52 GMT

Thanks a lot!