DATA Step, Macro, Functions and more

Check if the file exists inside of a macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Check if the file exists inside of a macro

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.

 

 


Accepted Solutions
Solution
‎05-09-2018 04:31 PM
Respected Advisor
Posts: 3,845

Re: Check if the file exists inside of a macro

For SAS data set you need to use EXIST function.

 

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

View solution in original post


All Replies
Respected Advisor
Posts: 2,802

Re: Check if the file exists inside of a macro

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

--
Paige Miller
Super User
Posts: 6,626

Re: Check if the file exists inside of a macro

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.;   
Occasional Contributor
Posts: 18

Re: Check if the file exists inside of a macro

Posted in reply to Astounding

Then I get

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

Super User
Super User
Posts: 7,932

Re: Check if the file exists inside of a macro


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

Occasional Contributor
Posts: 18

Re: Check if the file exists inside of a macro

Thanks a lot!

Super User
Super User
Posts: 9,397

Re: Check if the file exists inside of a macro

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

 

Occasional Contributor
Posts: 18

Re: Check if the file exists inside of a macro

[ Edited ]

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.

Super User
Super User
Posts: 9,397

Re: Check if the file exists inside of a macro

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

 

Occasional Contributor
Posts: 18

Re: Check if the file exists inside of a macro

[ Edited ]

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

Solution
‎05-09-2018 04:31 PM
Respected Advisor
Posts: 3,845

Re: Check if the file exists inside of a macro

For SAS data set you need to use EXIST function.

 

%sysfunc(exist(one-or-two-level-name))
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 125 views
  • 4 likes
  • 6 in conversation