How to deal with a logon timeout in SAS

Question

I have some SAS script that runs perfectly fine from the editor window . However when I use it as a batch file , sometimes it runs , sometimes it does not . The part that fails is :

1) libname ... /* I am connecting to a database
2) proc sql ( dsn= ... /* also connecting to a database

How can I write a code such that if the the libname and proc sql fail to establish connection , then try again . . . like a loop or something

Is there any option for libname and proc sql that would extend the timeout "dealine" ?

Please help me

I think you should be able to get a return code when it fails (_SYSERRTEXT_ if nothing else), but not 100% sure. Look at `%put _all_;` after it fails vs. after it succeeds. — Joe, Jul 14 '14 at 18:54

score 3 · Accepted Answer · edited May 23 '17 at 12:11

I don't know if this approach will work with an ODBC libname as well but I just tested this with a local libname and it worked fine:

data x;
  x = "blah";
run;

%macro loop_until_exists;
  %local result counter rc;

  %let result = %nobs(iDs=leadscor.x,iVerbose=1);
  %let counter = 1;
  %do %while (&result lt 0);  /* YOU MAY WANT TO ADD A TIMEOUT CLAUSE HERE */
    %put IT DOESNT EXIST YET =(;
    %if &counter = 3 %then %do; 
      libname leadscor "%sysfunc(pathname(work))";
    %end;

    %let rc = %sysfunc(sleep(1)); 

    %let result = %nobs(iDs=leadscor.x,iVerbose=1);
    %let counter = %eval(&counter + 1);
  %end;
  %put IT EXISTS =);
%mend;
%loop_until_exists;

Note that you will need the %nobs macro as defined in my post here: https://stackoverflow.com/a/5665758/214994

That code should run in your environment and you can then modify as needed.

How to deal with a logon timeout in SAS

1 Answers1