CODEWITHSUNDEEP
oracle-databaseerror-handlingsas
zachvac
zachvac

Reputation: 730

SAS Error Handling - Check after a data step to see if error was thrown

Hi I have a SAS job that runs successfully 90% of the time. But one of the steps relies on reading an oracle table that is occasionally being updated at the same time as I'm trying to read it. I implemented a check to see if it exists first before querying it, but since the pull takes ~15 minutes, it will sometimes exist at the start of the pull but not by the end which results in a SAS error.

What I want to do is gracefully catch this error, sleep for x time, and then attempt to re-run the same pull without the SAS job failing. Is there a way to do this in SAS? All the things I've searched rely on checking pre-conditions before the pull, but what can I do when those can change during the pull leading to an error?

Thanks.

Upvotes: 1

Views: 1051

Answers (2)

user667489
user667489

Reputation: 9569

One other approach that might be worth trying: if your oracle database will allow you to lock tables via SAS, try running a lock statement immediately before the data step. You can then check the result of the lock attempt via the &SYSLCKRC automatic macro variable, wait, and try again.

E.g. 

%macro wait_for_lock(DATASET);
  %let MINUTES_WAITED = 0;
  %do %until(&SYSLCKRC = 0 or &MINUTES_WAITED > 60);
    lock &DATASET;
    %if &SYSLCKRC ne 0 %then %do;
      data _null_;
        sleep = sleep(60);
      run;
    %end;
    %let MINUTES_WAITED = %eval(&MINUTES_WAITED + 1);
  %end;
%mend;

%wait_for_lock(oraclelib.mytable);

You can also use the FILEOCKWAIT system option to accomplish the same thing in more recent versions of SAS than the ancient one that I'm used to.

Upvotes: 0

Joe
Joe

Reputation: 63424

You can do this a bunch of different ways, but I think the old school method is probably best.

Assuming you're running this in batch mode - split your oracle pull into its own program, and call that program with its own call to SAS.exe. Have it put out a value (touch a file, say, or write the date or something to a file) and have the batch program look for that file/value. When that file/value is updated, then the batch program moves onto the rest of the process; if it's not updated, then sleep and re-call that program.

If you're doing this in Enterprise Guide, it's a bit easier as you can have a condition that does more or less the same thing (but you can actually check for error conditions via macro variables). You would need to not have SAS set to ABEND on an error, though.

Upvotes: 1

Related Questions