BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
keyreal
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
keyreal
Calcite | Level 5

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

View solution in original post

8 REPLIES 8
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Quentin
Super User

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

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
keyreal
Calcite | Level 5

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
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Quentin
Super User

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()
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Astounding
PROC Star

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
Super User

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 5288 views
  • 0 likes
  • 4 in conversation