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'
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);
Use scheduling software, its what it is made for and will be better than anything you will likely write.
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);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.