topic Suppressing Errors on PROC SQL in SAS Programming

Suppressing Errors on PROC SQL

Loadbasic — Thu, 06 Dec 2018 12:04:58 GMT

We have production jobs that use SAS and is designed to fail whenever SAS returns an error. Many of these production jobs read large amounts of data from external databases (Oracle, SQL server, etc.) via multiple queries, which will sometimes fail due to server issues.

I was wondering if there's a way to artificially catch these server errors and prevent SAS from throwing out an error itself; I wanted to make something similar to the .try/.catch system in other languages, where each DB query error would increment an internal counter and cause that query to restart; the job would abort after the internal counter reaches a certain threshold. This is to prevent the jobs from breaking after a single timeout error on a single query after 2 hours of running other queries, requiring a restart of a long job.

Thanks.

Re: Suppressing Errors on PROC SQL

Kurt_Bremser — Thu, 06 Dec 2018 12:17:35 GMT

There's two solutions:

the preferred one: spread your work over several successive jobs and let the scheduler keep track of success. No work is lost from the jobs 1 to n-1 if job n fails.
check within SAS for the &syscc automatic macro variable. It will be at zero as long as everything is OK.

The second method carries the risk that your SAS job is repeating a particular task even if that task CANNOT succeed at all, and thus wastes valuable time that could have been spent searching for and fixing the problem.

Re: Suppressing Errors on PROC SQL

ballardw — Thu, 06 Dec 2018 17:07:58 GMT

@Loadbasic wrote:

We have production jobs that use SAS and is designed to fail whenever SAS returns an error. Many of these production jobs read large amounts of data from external databases (Oracle, SQL server, etc.) via multiple queries, which will sometimes fail due to server issues.

Has your management team been apprised of how often these data sources fail? I would be very concerned if my databases are not accessible when actually needed.