SAS macro loop until obs count >0

Question

I'm trying to automate a scheduled event in SAS so that when the SAS program opens it first runs a pass through query that creates a local table. I want this query to keep running in a loop until the observation count of that table is >0. The idea is that I need to wait for a confirmation from the data source that the table is ready before I can run additional code to the server which I would do after the macro. I would also like it to sleep for 10 minutes each time the table shows no observations.

My ODBC connection...

proc sql;
connect to odbc(datasrc="SSDM"); 

EXECUTE ( Create Volatile Table DataReady as

(   
    SQL...

)   WITH DATA ON COMMIT PRESERVE ROWS;) by ODBC;

CREATE TABLE DataReady AS SELECT * FROM CONNECTION TO ODBC ( SELECT * FROM DataReady );

DISCONNECT FROM odbc;

quit;

and also include a sleep function...

data _null_;
rc=SLEEP(600);
run;

Chris J Chris J · Accepted Answer · 2018-11-16T21:26:08

Break it down into the key component parts

set initial number of observations counter to 0
Execute your queries
Check number of obs
If zero, sleep then loop back to #2

4 requires macro code (hence the macro wrapper LOOPER below), 2 & 3 are PROC / DATASTEP, and 1 could be either.

%MACRO LOOPER ;
  %LET OBS = 0 ; /* #1 */

  %DO %WHILE (&OBS = 0) ; /* run everything inside this %DO %WHILE loop  whilst 0 obs - #4 */

    /* put your existing SQL code here - #2 */

    proc sql ; select count(1) into :OBS from DataReady ; quit ; /* #3 */

    %IF &OBS = 0 %THEN %LET S = %SYSFUNC(sleep(10,60)) ; /* sleep for 10 minutes if 0 obs */
  %END ;
%MEND ;

%LOOPER ;

SAS macro loop until obs count >0

1 Answers