looping over time

[aj34321]

Loop starts at 7:00:00 and Should stop at 21:59:59
Inside the loop an Insert query is executed. This insert operation may take any number of minutes starting from 1 to 300 minutes or even more.
Once the Insert query is done, then find the next nearest 30th minute time and run the Insert query again.

Ex:
1. The first iteration started at 07:00:00 (1st iteration)
2. This took 12 mins to finish.
3. Wait for 18 mins.
4. Trigger SQL insert at 07:30:01 (2nd iteration)
5. 2nd Iteration query takes about 1 hr 11 mins - Which means the insert operation finished at 8:41:00
6. Wait for 19 mins.
7. Trigger SQL insert at 09:00:01 (3nd iteration)
8. 3rd Iteration query takes about 5 hr 37 mins - Which means the insert operation finished at 14:37:00
9. Wait for 23 mins.
10. Trigger SQL insert at 15:00:01 (4th iteration)

11. And so until 10pm. The moment 10pm occurs program should end.

Im not really good in this kind of time based loop hence need your advice / sample code to achieve this requirement. 

Request if any can help in this...

Thanks,

Anil

[Reeza]

Time is measured in number of seconds. So you can control your loop by determining the number of seconds things translate to. 

How are you measuring the time a process takes, or are you expecting to wait until a process is finished and then kick off the next process at nearest half hour?

Is the process the same every time or is it a different process?

[aj34321]

Yes, a wait is required until that process is finished and then kick off the next insert at the nearest 30 mins.

You are right, this approach will be same across all day.

[Reeza]

Use the DATETIME() function to determine how long to loop. It provides the current datetime and you can calculate,strength how long until next run. 

 You don't have a scheduling tool available? 

[Shmuel]

Use function:  call wait(seconds) in your loop:

do i=1 to 10;  /* 5 hours loop of 30 minutes */

     ... submit execution of the SQLto run in background ...

     call wait(1800);  /* 30 minutes * 60 seconds */

end;

[Shmuel]

With @Reeza's suggestion you can do your SQL inside your loop:

do i=1 to 10;  /* 5 hours loop of 30 minutes */

     _started_ = datetime();

    proc sql ....

    ...

    quit;

   _elapse_ = intck('minute' , datetime() , _started_ );

   _sec_to_wait_ = (30 - _elapse_ ) *60;

     call wait(_sec_to_wait_);  

end;

[SASKiwi]

You also need to consider what should happen if any one insert fails - do you just continue, retry or stop? What happens if the insert takes longer than 30 mins?

Why do you need to load precisely every 30 minutes in the first place? Is it because the data to be loaded is received every 30 minutes or for some other reason?