BookmarkSubscribeRSS Feed
SuryaKiran
Meteorite | Level 14

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
4 REPLIES 4
LinusH
Tourmaline | Level 20
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
SuryaKiran
Meteorite | Level 14

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

Thanks,
Suryakiran
Quentin
Super User

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;
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
SASKiwi
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 979 views
  • 3 likes
  • 4 in conversation