How to run sas job again if table has not updated?

[sampsas]

Hello Friend, i have question related to my previous post but its kind of different. Please help...

I developed a code which 

first - going to check

         if oracle date has today's date and if yes - then

              do;

                 process the code and send email once output is ready 

              end;

         else;

             do;

                send email that oracle table has not updated and sas is not going to process further. 

             end;

now,  my question is. how can i run same job again if oracle table has not updated? 

i am going to schedule this one on cron. idea is run jobs, lets say every five minute and check if oracle table has updated or not. if updated, run process (as mentioned above) and if not check again in 5 minutes. 

now, i cannot schedule this job in cron every five minute because what if first run find correct date and processed already and 2nd run is going to find same thing and run it again. goal is also to avoid any manual intervention and run job when data table has updated, if not updated check back in like 5 minutes...

/*code*/

%macro test;

   data _null_;
      call symputx('tday',put(today(),date9.));
   run;

  %put &tday;

   LIBNAME testlib ORACLE USER=xyz PASSWORD="xyz" PATH='whatever' SCHEMA=xyz;

   data check_date;
   /*align date and format it*/
    run;

    proc sql noprint;
    /*find out max date and macrovariable */
    quit;

    %put &maxdate;

%if &maxdate.=&tday. %then
       %do;

               data _null_;
                     filename mymail email "email"
                               subject=" ";

               data _null_;
                     file mymail;
                      put "table updated, process further";
               run;

               data test1;
                     set sashelp.stocks;
               run;

               proc export data=test1 outfile='/path_to_outputfile/testfile.txt' dbms=tab replace;
                      putnames=no;
               run;

               %let testfile1='/path_to_outputfile/testfile.txt';

                %if %sysfunc(fileexist(&testfile1)) 

                %then
                          %do;

                                 filename mymail email "email"
                                      subject=" ";

                                data _null_;
                                    file mymail;
                                    put "output files available";
                                run;

                           %end;
                    %else
                            %do;
                                  filename mymail email "email"
                                        subject=" ";

                                  data _null_;
                                        file mymail;
                                        put "file are not available,check log";
                                  run;

                           %end;
                      %end;
              %else
                        %do;
                               filename mymail email "email"
                                      subject=" ";

                               data _null_;
                                     file mymail;
                                     put "table has not updated yet";
                               run;

                       %end;
             run;

%mend;

%test;

[smijoss1]

1) the best solution is to keep a control table that adds record to it every time the process runs. So the first time the code runs it will add a record for current date, in your program check if a record exists for current date and if it does the do %RETURN  else let it proceed

Other options 

1)  IF the output a sas dataset - if so Lock the table and add the check in your IF condition to check if table is not locked only then proceed. (assuming you are appending data to an existing table). So next time cron job submits the sas code it will fail if its already running.


2)  have a control table taht keeps a flag indicating the process has started and have that checked. and the end of the process update it so next code sub knows whether any process is currently running or not.

3) create a temp file at start of process and delete it at end of process. Each new code sub should check if that file exists, if it does then %RETURN else proceed. 

[sampsas]

Thank you. 

i am sorry but i am not excellent programmer yet 😞 - if someone can please help with control table logic (i included my code logic in my question for ref). if i go with option 1) do i still have to schedule job on cron multiple times? like every 5 minutes for 1 hour? 

i am thinking of schedule cron jobs just once for this sas program and let SAS keep checking for date, if today's date is there in table, process rest of the code, and if not, sleep for lets say 5 minutes and run check again?

[ChrisNZ]

Your code is very hard to read.

Consider some code-writing practices that will make it more legible, like the ones used below.

We don't know exactly what you want, but I suspect the code below will be close to what you need.

Modify as required.

%macro test;

  filename OUTFILE '/path_to_outputfile/testfile.txt';

  data _null_;
    call symputx('tday'  ,put(today(),date9.));
    call symputx('crdate',' '                );
    if fexist('OUTFILE');
    FID=fopen('OUTFILE');                                                                                                                 
    CRDATE=input(finfo(FID,'Create Time'),anydtdtm.);
    FID=fclose(FID);                                                                                                                 
    call symputx('crdate',put(CRDATE,datetime9.));
  run;

  %put &=tday &=crdate;

  %if &crdate. = &tday. %then %do;
    data _null_;
      file mymail email ;
      put '!em_subject! Job status';
      put '!em_to!      email@address';
      put "output file already available ";
    run;
    %return;
  %end;

  LIBNAME testlib ORACLE USER=xyz PASSWORD="xyz" PATH='whatever' SCHEMA=xyz;

  data check_date;
  /*align date and format it*/
  run;

  proc sql noprint;
  /*find out max date and macrovariable */
  quit;

  %put &maxdate;


  %if &maxdate. ne &tday. %then %do;
    data _null_;
      file mymail email ;
      put '!em_subject! Job status';
      put '!em_to!      email@address';
      put "table has not updated yet";
    run;
    %return;
  %end;

  data _null_;
    file mymail email  ;
    put '!em_subject! Job status';
    put '!em_to!      email@address';
    put "table updated, process further";
  run;

  data test1;
    set sashelp.stocks;
  run;

  proc export data=test1 outfile=OUTFILE dbms=tab replace;
    putnames=no;
  run;

  data _null_;
    file mymail email  ;
    put '!em_subject! Job status';
    put '!em_to!      email@address';
    if fexist(OUTFILE) then do;
      put "output files available";
    end;
    else do;
      put "file are not available,check log";
    end;
  run;

  filename OUTFILE clear;

 %mend;

 
%test;
 

[sampsas]

Thanks. 

Thing is we don't know when oracle table is going to update (do not have exact time) so we want to see if we can run sas job every five minute and if table gets updated run the job and finish else keep checking every five minutes. 

[ScottBass]

---

@sampsas wrote:

Thanks. 

Thing is we don't know when oracle table is going to update (do not have exact time) so we want to see if we can run sas job every five minute and if table gets updated run the job and finish else keep checking every five minutes. 

---

Did you understand @ChrisNZ 's code?  His code addresses your comment above.  You just need to schedule it every 5 mins.  And you may have to modify it - with your generic description of your problem, it is impossible to spoon feed you perfectly working code.

As a start, why don't you hit the SAS doc for every command and function Chris listed in his code?

[ChrisNZ]

I agree with Scott that it does not look like you much spent time looking at the solution I am proposing.

Regarding scheduling, if you want to do this in SAS, you could run something like this:

data _null_;
  do until (datepart(CRDATE)=today());
    rc= dosubl('%test');                              
    if ^fexist('OUTFILE') then call sleep(300,1);
    else do;
      FID=fopen('OUTFILE');                                                                                                                 
      CRDATE=input(finfo(FID,'Create Time'),anydtdtm.);
      FID=fclose(FID);                                                                                                                 
      if ^(datepart(CRDATE)=today()) then call sleep(300,1);
    end;
  end;
run;

The filename must now be outside of the macro.

The macro can be simplified since some of the vetting now takes place here.

[ScottBass]

The issues I see with your approach (after a quick skim only):

1) You're sending an email every 5 mins when the data hasn't been updated yet.  That would annoy me to get these "nothing" emails.

2) You're launching SAS every 5 mins to check if the data has been updated.  That could be done using Oracle utilities (I think?) and avoid the overhead of launching SAS just to launch an Oracle query.

This is how I'd approach it:

Write a bash script that would:

1) Launch an Oracle command line query to check if the data has been updated: max(date) >= today's date.  If so,

2) Check a text file to see if the SAS program has run.  Use the tail -1 command to read the last line of the file.  If SAS has run, abort the script.  You could also check your logs directory for the appropriate timestamped log from your job - that would also work, but could be problematic if your program failed and should be re-run.

(You could reverse #1 and #2 - whatever works best for you)

3) Ok, so Oracle has updated and the SAS program hasn't run, so launch SAS.

4) Run your program.  

5) At the end of your program, check &syscc.  If there have been no errors, append a line to your text file log indicating your program has run.

6) Send your email indicating success or failure of your program.

7) Schedule your bash script to run every 5 mins.

😎 Your text file log is a quick check for you to see if you program has run successfully.

Much of this logic could be avoided if you have a more robust scheduler than cron.

There are many ways to approach this - I'm sure others will suggest alternative approaches.

i am sorry but i am not excellent programmer yet  - if someone can please help with control table logic

Is there a colleague who can assist?  Do you know shell scripting?  Do you know whatever command line utilities Oracle provides to execute a query and return the results to the calling script?  Do you know how SAS appends data to an external file?  If not, Google and the SAS doc are your friends.

[Kurt_Bremser]

Keep a table of dates (or datetimes) for which your code has run.

Check the max date value of the Oracle table against the max date in your check table.

If same, do nothing.

If Oracle table max is greater than your check max, run your code, and add the new max to your check table.

[sampsas]

Thank you all and really appreciate your time. and i am really sorry but i don't need line by line code from you. Trying to understand logic and to see where exactly i can insert your suggestion in my existing code. i never implemented such logic so hard for me to understand your input you are providing. i will be extra careful when asking question next time. 

just have one question if you can answer, if i go with below option, what is "outfile" in your code? (because, i don't receive any touch file when oracle table gets updated, i will have to keep checking against oracle table only)

data _null_;
  do until (datepart(CRDATE)=today());
    rc= dosubl('%test');                              
    if ^fexist('OUTFILE') then call sleep(300,1);
    else do;
      FID=fopen('OUTFILE');                                                                                                                 
      CRDATE=input(finfo(FID,'Create Time'),anydtdtm.);
      FID=fclose(FID);                                                                                                                 
      if ^(datepart(CRDATE)=today()) then call sleep(300,1);
    end;
  end;
run;

[ChrisNZ]

> what is "outfile" in your code?

The answer is in this element of my reply, which you seem to have missed:

The filename must now be outside of the macro.

This data step checks the creation date for the file and waits if it's not today or if there is no file.

[smijoss1]

How do you ensure you code isnt triggered when the load is still happening ? (Unless its very small data and the load is instant i would worry about the process picking partial data.

[smijoss1]

WAIT  all you are doing is dumping the Oracle table into an Text file. For that you are downloading the Oracle table into SAS and then exporting. Too much data transfer if the Table is huge. 

I would suggest using Oracle Script