Execute Proc SQL Statement inside a Do While or Do Until Statement

Question

I have a sql database that updates anywhere from 4:00 am to 7:00 am in the morning. I want to run an automated program that will extract the data once it is available. It currently check every 30 minutes. I have written a SAS program that will accomplish this but I would like to condense the code and execute it in a true loop. Below is my current code. I repeat the block of code 1 a total of seven times. If the data is available on the first check then my code will still execute 7 times. I would like it to end after the first time it finds data in the table. I have tried relentlessly to create a loop but have failed in all efforts.

%Let RecordCount = 0;/*Sets initial Record Count to 0*/
%Let min = 30;

/Determines how many minutes SAS will wait till it attempts to requery the table after receiving 0 records/

data _NULL_; /*Get the previous Working Day based on todays date*/
  DateCheck = weekday(Today());
  Select (DateCheck);
  When (1) Do; 
        call symputx('_ReportDt',intnx('day',Today(),-2));
  end;
  When (2) Do;
    call symputx('_ReportDt',intnx('day',Today(),-3));
  end;                                    
  otherwise do;
    call symputx('_ReportDt',intnx('day',Today(),-1));
  end;
  end;
run;

/****************************1***************************/

Proc Sql noprint; 
  Select Count(ACCOUNT_NUMBER)
  Into :RecordCount separated by ' '
  From Table1 
  WHere Date = &_ReportDt;
Quit;

data _null_;
if &RecordCount = 0 then do;
                            wait_sec= (60*&min);
                            time_slept = sleep(wait_sec,1);
                        end;
                    else do;
end; 
run;

If you want to use macro conditional logic statements like `%DO` or `%IF` then you need to wrap your code into a macro definition and then call the macro to run it. Did you try that? — Tom, Dec 21 '16 at 17:41
I did but did not have any success. I also tried utilizing a macro and using call execute. Is it because I am trying to mix Proc SQl with a data step? I would like to run proc sql to see if any records are returned. If they are then end program. If not then execute the sleep for 30 minutes. Once its been 30 minutes then execute proc sql again. If records are returned then end program else wait 30 minutes again and repeat. — Mark, Dec 21 '16 at 18:11

score 0 · Accepted Answer · edited Dec 28 '16 at 20:07

I was able to find a solution. Below is the code I was able to get to work. I hope this can help someone else.

%MACRO SLEEP(MINUTES);
 DATA _NULL_;
 wait_sec= (60*&MINUTES);
 Var1 = sleep(wait_sec,1);
 RUN;
 %MEND SLEEP; 

 %MACRO DataCheck();
    Proc Sql noprint; 
        Select Count(ACCOUNT)
        Into :RecordCount separated by ' '
        From Table
        Where Date = &_ReportDt;
    Quit;
%DO %WHILE (&RecordCount = 0);
    Proc Sql noprint; 
        Select Count(ACCOUNT)
        Into :RecordCount separated by ' '
        From Table
        WHere Date = &_ReportDt;
    Quit;
    %SLEEP(15);/*Insert the number of Minutes that you want the program to sleep in between data checks.*/
%END;
 /*PROCESS*/
 %PUT "IT WORKED!";
%MEND DataCheck;

 data _null_;
 %DataCheck;
 run;

Execute Proc SQL Statement inside a Do While or Do Until Statement

1 Answers1