DATA Step, Macro, Functions and more

Schedule SAS Job after table is avaliable

Reply
Frequent Contributor
Posts: 136

Schedule SAS Job after table is avaliable

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!

 

Thanks,
Suryakiran
Super User
Posts: 5,426

Re: Schedule SAS Job after table is avaliable

Posted in reply to SuryaKiran
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.
Data never sleeps
Frequent Contributor
Posts: 136

Re: Schedule SAS Job after table is avaliable

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

Thanks,
Suryakiran
PROC Star
Posts: 1,322

Re: Schedule SAS Job after table is avaliable

[ Edited ]
Posted in reply to SuryaKiran

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;
Super User
Posts: 3,252

Re: Schedule SAS Job after table is avaliable

Posted in reply to SuryaKiran

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.

Ask a Question
Discussion stats
  • 4 replies
  • 134 views
  • 3 likes
  • 4 in conversation