topic Schedule SAS Job after table is avaliable in SAS Programming

Schedule SAS Job after table is avaliable

SuryaKiran — Sat, 22 Apr 2017 17:34:05 GMT

Hi SAS Users,

I have a table in Oracle which gets updated daily manually. I want to pull some data from that table after it was updated and want to schedule this process. I can't schedule a job when ever I wish. I can put my code in a folder that was setup for scheduling the jobs. If I place my program.sas file in that folder the job will be scheduled based on queue in folder at 12.01 AM every day.

I can get the timestamp when the table was refreshed using ALL_TAB_MODIFICATIONS table in Oracle.

proc sql;
connect to odbc as mycon
   (datasrc=ABCDEF user=surya password=xxxxxx);

select Timestamp INTO: Timestamp
   from connection to mycon
      (select * from ALL_TAB_modifications
		Where Table_owner='ORCL' and Table_name='TABLE_ORACLE';);

disconnect from mycon;
quit;

%put &Timestamp;

My program to schedule is as follows:

proc sql;
connect to odbc as mycon
   (datasrc=ABCDEF user=surya password=xxxxxx);
Create table test1 as
select *
   from connection to mycon
      (select * from ORCL.TABLE_ORACLE
  where Name = 'SURYA');

disconnect from mycon;
quit;

I'm looking code for something like, if my program starts running at 12.01 AM it has to sleep untill 9:00 am and from then it has to look for the table avaliability every one hour. i.e at 9:00 AM it has to check for table avaliability if avaliable then run my query and stop loop or sleep for 1 hour and again check at 10:00AM if avaliable run the query and stop loop or sleep for 1 hour........untill 5:00PM. Finally if the update of table is not found untill 5 pm then stop process and repeat next day.

Thanks in Advance!

Re: Schedule SAS Job after table is avaliable

LinusH — Sat, 22 Apr 2017 20:47:17 GMT

This sounds awkward.
My immediate feeling is that it should be possible from the Oracle side do some kind of push notice of the modification.

Also, if you are on UNIX/Linux or Windows, you sjoule be able to use crontab/Windows task which let you schedule a job when you want, like the hours you mention. Then there is no need to build a loop. Then your query would either check that the modification time is within the last interval, or you build some logic on the SAS side with a timestamp in log data set.

Re: Schedule SAS Job after table is avaliable

SuryaKiran — Sat, 22 Apr 2017 21:20:03 GMT

I don't have any other option then doing in this way.

Re: Schedule SAS Job after table is avaliable

Quentin — Sun, 23 Apr 2017 11:54:16 GMT

Agree with @LinusH, writing you own scheduler in SAS will be awkward at best. And will waste resources, as your SAS session will be running for 17 hours a day, even though most of that time is sleeping.

That said, there are plenty of user group papers that discuss how to do this sort of scheduling by using the sleep() function (search lexjansen.com).

Some approach like below pseudo code should be feasible. Note that on windows sleep(60) is 60 seconds, but on linux it's 60 miliseconds.

%macro doit() ;           
  %local rc done ;

  %let rc=%sysfunc(sleep(60*60*9)) ; *sleep 9 hours (from SAS session start at midnight);

  %let done=0;

  %do until( (&done) or (%sysfunc(time()) > (60*60*17))  ) ;   %*do until done or 1700;
    
    *Run your query ;
    
    %if <some return code from your query is happy> %then %let done=1 ;
    %else %let rc=%sysfunc(sleep(60*60)) ;  %*sleep an hour;

  %end;
%mend;

Re: Schedule SAS Job after table is avaliable

SASKiwi — Sun, 23 Apr 2017 00:50:20 GMT

If you are not allowed to schedule jobs yourself, can your SAS administrator do it for you? Using the SAS SLEEP function as @Quentin has suggested is a great way of delaying your job until the data is available. I do something similar myself, using a macro loop to check if a table has been updated every 15 minutes or another specified interval.