SAS connecting to another server is randomly blocked with an error. Can I tell SAS to repeat until successful?

I use SAS to connect to a remote SQL database that is experiencing some issues. It randomly blocks the connection attempt. Right now our only recourse is to hang around resubmitting the program until the connection is made.

I'd like SAS to repeatedly try to connect to the remote server until successful. If I could parameterize things like waiting between repeated attempts and capping the number of attempts, that would be great.

Note that I'd like to do this within SAS since we have programs that touch the remote database, do some stuff in SAS, use those results to touch the remote database again, and so on.

Thanks.

EDIT: I'm using SAS 9.4 and Viya.

EDIT: The code is pretty generic and it only recently started. We're often running code that has worked for years. The main way we use the SQL database is with the pass through method. Referencing the link below, the failure occurs at "connection to oracle ..." and the problem is with the SQL server. If you simply wait and run the identical code again, some fraction of the time it will run successfully. This only started a few weeks ago but for some reason is giving IT fits. Nonetheless, we still have work to do so we wait and resubmit programs until the connection is made.

I'd like code that wraps around that "connect to oracle ..." line repeating it until the connection is successful.

https://documentation.sas.com/doc/en/lrcon/9.4/n1kbstf7vw0qcjn1ibfc8c78a9lc.htm

Solution

Use the automatic macro variables that the CONNECT statement creates.

Any return code or message that the DBMS generates is available in the SQLXRC and SQLXMSG macro variables after the statement executes. For information about these macro variables, see Macro Variables for Relational Databases.

To really implement a loop in the middle of a PROC SQL step you will need to use a macro to generate the code.

Say you want to generate this CONNECT statement.

connect to oracle (user=myusr1 pw=mypwd1 path='mysrv1');

First create a macro that will repeatedly tries to make the connection. For now let's just make one that tries 10 times and waits 10 seconds after a failure. So something like this:

%macro connect;
  %local n good rc;
  %let good=0;
  %do %until( &good or (&n>10) );
    %let n=%eval(&n+1);
connect to oracle (user=myusr1 pw=mypwd1 path='mysrv1');      
    %if (&sqlxrc and (&n<10) ) %then %do;
      %put Try number &n failed SYSXRC=&sysxrc.. Will wait 10 seconds and retry.;
      %let rc=%sysfunc(sleep(10,1));
    %end;
    %else %let good=1;
 %end;
%mend;

Then in your actual code place a call to the macro in place of the connect statement itself.

 proc sql;
   %connect;
   create table want as select * from connection to oracle
     ( some oracle query here)
   ;
 quit;

You might also want to instead create a LIBREF that points to the database. That could be simpler because then you could use a data step to create the libref using the LIBNAME statement. Since you can code a loop in a data step that would avoid the need to create a macro just to allow looping.

Once you have a working libref then you can use the CONNECT USING syntax of PROC SQL to re-use the connection created for the libref instead of trying to create a new connection. For example if the libref is named MYLIB then your PROC SQL step would just be:

 proc sql;
   connect using mylib;
   create table want as select * from connection to mylib
     ( some oracle query here)
   ;
 quit;