Check multiple files existence in a %DO %UNTIL macro...

[keyreal]

Hello,

I have a table with a single column (sas_datasets) that has the names of files as its values, i.e.

sas_datasets

-------------------------------------

/work/users/file1.sas7bdat

/work/users/file2.sas7bdat

...

I use the following

/* Note that I inserted the asterisk (*) after the percent sign (%) below to prevent it from evaluating to 0, instead I need it as text */

proc sql noprint;

select cats('%*sysfunc(fileexist(','"',sas_datasets,'"))')

into :untils

separated by ' AND '

from sas_datasets

;quit;

/* Here I remove the asterisk */

%put NOTE: %qsysfunc(compress(&untils,*));

resolves to

%sysfunc(fileexist("/work/users/file1.sas7bdat")) AND %sysfunc(fileexist("/work/users/file2.sas7bdat"))

/* CHECK IF SAS DATASETS ARE READY */

%macro fex;

/* Here I want this to resolve to 1 if both files exist and to 0 otherwise */

%do %until ( %eval(%unquote(%qsysfunc(compress(&untils,*)))) );

  %put NOTE: Not There Yet!;

  %macro sleep;

  data _null_; x = sleep(1000); run;

  %mend sleep;

  %sleep;

    dm 'clear log';

%end;

%mend fex;

%fex;

The %macro fex fails with the following errors:

ERROR: Expected close parenthesis after macro function invocation not found.

ERROR: %EVAL function has no expression to evaluate, or %IF statement has no condition.

ERROR: %EVAL function has no expression to evaluate, or %IF statement has no condition.

ERROR: The condition in the %DO %UNTIL loop, %eval(%unquote(%qsysfunc(compress(&untils,*)))), yielded an invalid or missing value, .

The macro will stop executing.

ERROR: The macro FEX will stop executing.

Please help me resolve this issue!

Thanks

[Quentin]

Hi,

I would suggest %DO %WHILE instead.  Something like blow maybe?

data sasdatasets;
  input datasetname $41.;
cards;
work.a
work.b
;
run;

%macro dosomething(dummy);

%local AllPresent i;
%let i=1;

proc sql noprint;
  select min(exist(datasetname)) into : AllPresent
  from sasdatasets;
quit;

%do %while (&allPresent=0 and &i

--Q.

[Quentin]

Looks like my first post was truncated by a LT character which loooked like an open tag....

data sasdatasets;
  input datasetname $41.;
cards;
work.a
work.b
;
run;

%macro dosomething(dummy);

%local AllPresent i;
%let i=1;

proc sql noprint;
  select min(exist(datasetname)) into :AllPresent
  from sasdatasets;
quit;

%do %while (&allPresent=0 and &i lt 10);
  %put i=&i Not all here yet;
  %let dummy=%sysfunc(sleep(1));
  %let i=%eval(&i+1);
%end;

%mend dosomething;

%dosomething()

Message was edited by: Quentin McMullen

[keyreal]

Quentin, thank you for you responses!

I think the problem is not whether to use UNTIL or WHILE loop but rather the fact that

the macro variable that resolves to this statement does not work in the loop:

%sysfunc(fileexist("/work/users/file1.sas7bdat")) AND %sysfunc(fileexist("/work/users/file2.sas7bdat"))

Also, the fact that I have the filenames in the table does not mean that they already exist. I just know what the file naming convention is, but the files themselves are not yet being created. Therefore, I use DO UNTIL to constantly (at certain frequency) pass the file existence statement repeatedly until both files exist. I think it has to do with macro quoting but I cannot figure it out.

Thanks

[Quentin]

Only benefit of %DO WHILE is that if they did both exist on the first iteration, you wouldn't have to sleep.    But agree that's a small point.

Did you try my example?  I think it's doing what you want.

--Q.

[Quentin]

Hi Again,

Glad you got it working.

Just to correct myself, realized on the drive home that I had forgotten to check for existence of the datasets at the end of each loop, after the sleep.

Below is what I had meant to suggest.

Basically instead of using the SQL step to build a macro variable that will resolve to code (your ANDed list of FileExist checks), the SQL step here returns a 1 if all datasets exist,  0 if any does not exist.  So it's an alternate approach that avoids the macro quoting headaches.  It probably runs slower than your solution (cuz the SQL step should take longer than the %EVAL of your ANDed list), but of course speed doesn't matter when you are just waiting for something to arrive.

I used exist instead of fileexist as a convenience, since they are SAS dataasets.

data sasdatasets;
  input datasetname $41.;
cards;
junk.a
junk.b
;
run;

libname junk "d:\junk";

%macro dosomething(dummy);

%local AllPresent i;

%*Check to see if all datasets (junk.a and junk.b) exist;
%*If all exist, min(exist(datasetname)) is 1;
%*If any does not exist, min(exist(datasetname)) is 0;
proc sql noprint;
  select min(exist(datasetname)) into :AllPresent
  from sasdatasets;
quit;

%let i=1;

%*if any dataset doesnt exist, sleep and then check again;
%*Will sleep a maximum of 10 times and then give up;
%do %while (&allPresent=0 and &i le 10);  
  %put Iter#&i: Missing at least one dataset, sleep and try again.;
  %let dummy=%sysfunc(sleep(5));

  %*Check to see if they all exist now;
  proc sql noprint;
    select min(exist(datasetname)) into :AllPresent
    from sasdatasets;
  quit;

  %let i=%eval(&i+1);
%end;

%if &allPresent=1 %then %do;
  %put All datasets exist! Continue processing...;
  %* Main code /macro call here;
%end;
%else %if &allPresent=0 %then %do;
  %put Giving up! Still missing at least one dataset.;
%end;

%mend dosomething;

%dosomething()

[Astounding]

keyreal,

Worth a try, to isolate the problem.  Instead of adding a complex argument to %do %until, use a simple one:

%do %until (&result=1);

   ...

   %let result = %eval(%unquote(etc.));

%end;

On a side note, when %sysfunc calls fileexist, the quotes around the file names are optional.  If it's simpler for you, you could remove them.

Good luck.

[ballardw]

Minor note, you probably should move the definition of the Macro SLEEP out of the FEX macro. You are recreating the exact same macro in each step through the loop. If this is a place holder for another macro as a test it could be even more effecient not to recompile the macro multiple times.

[keyreal · Accepted solution]

Thank you all for your suggestions! I finally figured it out!

/* I needed to mask the %-sign in the sysfunc using %nrstr() at compilation */

proc sql noprint;

select cats('%nrstr(%sysfunc)(fileexist(','"',sas_datasets,'"))')

into :untils

separated by ' AND '

from sas_datasets

;quit;

/* CHECK IF SAS DATASETS ARE READY */

%macro fex;

/* Here I evaluate the existence of multiple files to be 1 if all of them exist and 0 otherwise */

%do %until ( %eval(%unquote(&untils)) );

%put NOTE: File does NOT exist!;

%macro sleep;

data _null_; x = sleep(1000); run;

%mend sleep;

%sleep;

dm 'clear log';

%end;

%mend fex;

%fex;

%put NOTE: ************* SUCCESS!!!!!!;