01-03-2018 10:18 AM
When running PROC SQL and an error occurs, how to tell SAS to ignore the error, pause for a few seconds, and rerun the query?
The background is: I'm pulling data from the server using PROC SQL. However, the server is not stable and sometimes the connection error occurs (as the picture shows below). Simply rerun the query can resolve the issue. But since it's a loop of many queries wrapped in a macro function, also it takes hours to run through the whole loop, it's not feasible to stop rerun the query manually. So I'm wondering if I can add this logic to this marco:
If connection error occurs -> hold for a few seconds -> rerun the query that the error occurs -> next iteration
01-03-2018 10:39 AM
Sure - there are a few options here.
If you're running in Enterprise Guide, you can use some of the options related to flow control in the process flow. Put your query in a single program node, then have a conditional that looks at a macro variable (perhaps &sqlobs or &sqlrc) and reruns the program if it looks like it failed (if &sqlobs=0 or undefined, or if &sqlrc is not 0).
If you're doing it in base SAS, you'll be able to do the same thing; you'll just have to write a macro loop that has an exit condition of &sqlrc = 0.
%do %until (&sqlrc = 0);
proc sql; [your code]
Of course you may want to also put a counter in there that exits after 10 tries or something.
This assumes you're not running in batch; in other words, that you're not running in a situation where an error puts you into syntax check mode and OBS=0. If that's the case, that won't work as your program will just fail after this, and you'll want to either change those options or use an external to SAS (could be powershell, or similar) to rerun the batch process when it fails.
01-03-2018 10:44 AM - edited 01-03-2018 11:09 AM
You've blocked out so much code it isn't possible to tell if each of your SQL calls is connecting to the same DBMS. Perhaps making one connection and not disconnecting after each Proc SqL call might simplify things. It may be that you server is unavailable as it is still busy cleaning up from the previous connection.
You might try investigating the value of the automatic macro variable SYSERR after each of those connects.
%If &syserr > 6 %then %do ;
the something might be to call the sleep function (%sysfunc(sleep(10)) for example would suspend the program for 10 seconds) and then
horror of horrors use a %goto to go back to the last step needed.
You might also investigate any settings related to you connect approach.
I have to say that a server this as unstable as you imply isn't much of a server.
01-03-2018 10:47 AM
There are SO many SAS error macro variables, I can't remember which (if any) are reliably set by this sort of error. That said, assuming there is one (like &sqlrc? ) which would be set to zero when it succeeds, non-zero when it errors, you could do something like (untested):
%macro runquery(...); %do %until(&sqlrc=0); proc sql ...; quit; %if &sqlrc ne 0 %then %do; data _null_; x=sleep(60); *Sleep 60 seconds on windows, 60 miliseconds on Linux; run; %end; %end; %mend;
Could add some time-out logic to avoid an infinite loop.
01-03-2018 11:02 AM
This is the problem: "However, the server is not stable and sometimes the connection error occurs"
Fix that problem, i.e. make the server stable. Its like saying, sometimes when driving, my wheel falls off, how can I set some bricks there to catch it when the wheel comes off. You don't, you get the wheel fixed on properly like it should be.
01-03-2018 11:47 AM
However, the server is not stable and sometimes the connection error occurs (as the picture shows below).
Can the you-know-what and switch to a solid server with solid software. Flaky DBMS infrastructure must not be tolerated.
Here I absolutely concur with @RW9
If you are very remote from the server and have network problems, switch to unloads to flat files and transfer those with a robust sftp.
01-03-2018 01:27 PM
While infrastructure investments make sense, it's also reasonable to write fault-tolerant applications, to handle situations where a database or other expected resource is unavailable or locked. "Handle" sometimes means wait and retry. Troy Hughes has a nice book on approaches to doing this in SAS (and plenty of user group papers as well). https://www.amazon.com/SAS-Data-Analytic-Development-Dimensions/dp/111924076X
01-03-2018 08:41 PM - edited 01-03-2018 08:48 PM
I'm very much with @RW on the same page that you shouldn't try to patch the problem but have the root cause resolved.
Using the error message for Googling...
TCP Provider: Timeout error 
...it appears this is not an uncommon issue and the first step could be to increase timeout settings.
As this issue seems to be related by too many login requests at the same time, you could also try and reduce the login requests from your SAS program by using a global connections (=reusing already established connections).
If you've got also other connection problems like interrupted connections for long running queries then you need to talk to your DBMS and Network guys.
SAS doesn't have a SQL retry option. You could eventually implement such a try-catch mechanism but it feels like quite a bit of work to make this stable and it's certainly something I'd do only for some isolated critical use case and not something for every single SQL you issue because adding such try-catch logic will most likely complicate code development and maintenance significantly.