I am looking to create a regular script or macro allowing me to:
Using SAS/CONNECT: libname MAIN odbc user=***** password=*****dsn= myoracle schema=Table1 preserve_tab_names=yes;
Pseudo code:
If username connects to table then do;
Run oracle script 1;
Run oracle script 2;
End;
Try the Libref function - this checks whether a libref has been assigned and returns 0 if it has and a non-zero value if not - the full documentation is here
If you issue the libname statement only once then only one attemt gets made to connect to the database. Just using the libref downstream won't create another connection attempt (especially as you're not using option defer=yes).
If your code tries to connect multiple times then you must have code with multiple libname statements.
In case you run all these 3 SQL's in the same session then just have a single libname statement on top of your script.
What at @ChrisBrooks proposes would allow you to check if a connection could get established and lets you implement code that ends in a controlled manner if something goes wrong. It wouldn't protect you though from SAS making a connection attempt first and your account would still get locked out after 3 such unsuccessful attempts.
One way to get around this:
Have a SAS Admin talk with the Oracle DBA's so he/she can define an authentication domain in SAS Metadata using a generic account where the password never expires or where admins managed the generic account and keep passwords in sync becomes an IT function. With such a domain defined you won't need to pass credentials in your libname statements anymor, you just need metadata read access to the definition in SAS Metadata.
http://support.sas.com/kb/38/204.html
If that's not possible then another option I can think of:
1. Implement a SAS Autocall macro
2. Have the macro issue the libname statement
3. Build in the check as proposes by @ChrisBrooks
4. Maintain a permanent control table in SAS where you write an entry in case you couldn't establish a connection
If possible: Only write this entry if the reason was an expired password. Not sure though if you get a specific return code for this.
5. In your macro always check first your control table for an entry of failed connection attempt and only issue the libname statement if there hasn't been such a failed attempt.
...and just as an idea: You could also add a timestamp to the entries for failed attempts and only consider entries not older than 30 minutes or so. Or you just have an adhoc script which cleans out the control table once you've changed your password.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.