BookmarkSubscribeRSS Feed
stat_joey
Calcite | Level 5

Hi folks, 

 

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   

 

Thank you!

The second query is almost the same as the previous one, and runs right after it. But due to connection issue, the second query doesn't run throughThe second query is almost the same as the previous one, and runs right after it. But due to connection issue, the second query doesn't run through

7 REPLIES 7
snoopy369
Barite | Level 11

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]
quit; %end;

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.

ballardw
Super User

 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 ;

   <something>.

%end;

 

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.

Quentin
Super User

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.

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.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

@stat_joey wrote:
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.

Quentin
Super User

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

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.
Patrick
Opal | Level 21

@stat_joey

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 [258]

...it appears this is not an uncommon issue and the first step could be to increase timeout settings.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/35d02688-b2c5-47ab-99c2-050d62aa2277/tcp-pr...

 

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).

http://go.documentation.sas.com/?docsetId=acreldb&docsetTarget=n166tqlopg9p0bn1okz4ilmde7mp.htm&docs...

 

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.

 

 

 

 

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
  • 7 replies
  • 5037 views
  • 10 likes
  • 7 in conversation