topic Re: Help with loop needed -- code suggestions and examples needed in SAS Programming

Help with loop needed -- code suggestions and examples needed

ijmoorejr — Mon, 29 Oct 2018 06:42:09 GMT

I am attempting to write some code, possibly a do until loop, that will keep running the following proc sql block until all of the conditions in the where clause are met. Once the conditions are met, I need to run an X command. What is the best way to code this? Please provide sample code. Thanks.

proc sql;

create table trigger as

select Name, TDate, Status

from TDB.TTABLE

where Name="Load" and TDate=today() and Status="Ready";

quit;

 

x 'ksh /path/to/file/kshell.ksh'

Re: Help with loop needed -- code suggestions and examples needed

Peter_C — Mon, 29 Oct 2018 08:15:49 GMT

Sleep function provides a controlled delay until retesting to see if the filter returns a non-empty set.
I don't see how a data step could use the DO UNTIL() control as (iirc) a data step stops when the WHERE executes and returns an empty set.

Re: Help with loop needed -- code suggestions and examples needed

RW9 — Mon, 29 Oct 2018 08:34:58 GMT

Use scheduling software, its what it is made for and will be better than anything you will likely write.

Re: Help with loop needed -- code suggestions and examples needed

MichaelLarsen — Mon, 29 Oct 2018 08:46:41 GMT

I agree with RW9 that scheduling software may be the best solution.

But I did create an example for you to try, but tested on Windows. I would expect the same behaviour on Unix.

%macro waitfor_new_data(Waittime=1, Maxtries=5);
  %let Try=1;
  %let trigger=0;
  %* Loop until either new data is ready or max number of tries reached.;
  %do %until(&trigger = 1 or &Try > &Maxtries );
    proc sql noprint;
      select 
        count(*) > 0 format=8.
        into : trigger trimmed
      from TDB.TTABLE
      where Name="Load" and TDate=today() and Status="Ready";
    quit;
    %let Try=%eval(&Try+1);
    %if &trigger = 0 and &Try <= &Maxtries %then %do;
      %put NOTE: Waiting for &Waittime seconds...;
      %let rc=%sysfunc(sleep(&Waittime,1));
    %end;
  %end;

  %if &trigger = 0 %then %do;
    %put NOTE: Max tries executed and no new data found, execution stopped.;
  %end;
  %else %do;
    %put NOTE: New data found, executing script.;
/*x 'ksh /path/to/file/kshell.ksh';*/
  %end;
%mend;

/* Create a test table */
libname tdb "C:\Temp";
data tdb.TTABLE;
  Name = 'Load';
  TDate = today()-1;
  Status = 'Ready' ;
run;
%waitfor_new_data(Waittime=2, Maxtries=5);

Re: Help with loop needed -- code suggestions and examples needed

MichaelLarsen — Mon, 29 Oct 2018 08:48:15 GMT

For the example to find new data you should remove the -1 in the data step that generates the test table.