04-22-2017 01:34 PM
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!
04-22-2017 04:47 PM
04-22-2017 06:10 PM - edited 04-23-2017 07:54 AM
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;
04-22-2017 08:50 PM
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.