The use of automated scripts have made it extremely important to understand error conditions in a programmatic way. Here are a couple of the ways you can check for errors in your automated SAS scripts.
SYSERR - This macro variable should have a value of 0 (zero) if the data or proc step completed successfully.
put something here;
%put NOTE: This step returned a value of $SYSERR;
SYSCC - This is the value that SAS would return to the operating system if it were to exit at that point. You can use this to see if there were errors in your script up until this point. The value of SYSERR is reset upon each data or proc step. However, SYSCC doesn't reset unless the user resets it within the script. This can be useful if you want to force SAS to exit in a success or fail status.
NOTE - It is important to know that it is well documented that both of these variables have flaws. There are conditions that don't trigger errors that might not be intuitive. If you are relying heavily on these variables for code success, you should take a minute to read the SAS documentation around what does and doesn't constitute an error.
Here is a great macro that you can be use to check for database connections. The macro will attempt to connect to the database and return a value when complete signifying success or failure.
You can specify the variables via macro parameters or filling in the details within the macro.
%local sleep_statement connected_to_database;
Program Name : check_db_connect_ah.sas
Purpose : checks database connection
Author : HoodA (www.AFHood.com)
You may pass the values in via the macro parameters or by setting the
values within the macro itself.
Only checks one database connection. Must run multiple times for each
database connection you want to check.
This macro will return a variable, "database_connection" with
a value of YES or NO;
2009.03.01 - hooda - Macro created
/* Settings */
%* USE THESE SETTINGS IF YOU DONT PASS THE VARIABLES INTO THE MACRO
VIA THE MACRO PARAMETERS ;
%let connect_to_string_default=oracle(user=xxxx orapw=xxxx path=xxxx ); /* change this to meet your needs */
%let time_to_sleep_default=10; /* number of minutes to wait between connection attempts */
/* Settings */
%* checking for passed parameters ;
%if (&max_connection_attempts ="" or &max_connection_attempts le 1 ) %then %do;
%if ( %length(&connect_to_string) < 5 ) %then %do;
%if ( &time_to_sleep le 1 ) %then %do;
%* this is the actual sleep statement ;
%put Attempting to connect to Database;
%put Using string: &connect_to_string;
%put Trying to connect a maximum of &max_connection_attempts times;
%put waiting &time_to_sleep minutes ( or &sleep_statement seconds );
%put between attempts.;
%* attempt to connect up to x times at x minute intervals,;
%* until successful or out of time;
%do i = 1 %to &max_connection_attempts;
connect to &connect_to_string;
%* if the query succeeded, exit the loop ;
%if ( &SQLXRC=0 ) %then %do;
%if ( &i = 1 ) %then %do;
%put Connection succeeded after 1 attempt.;
%put Connection succeeded after &i attempts.;
%* if the connection failed, wait 10 minutes and try again;
filename sleepout pipe "sleep &sleep_statement";
%* connection should have been made or reached max attempts;
%* we will return a result to the program, ;
%* YES or NO as a value of database_connection. ;
%if &connected_to_database=YES %then %do;
disconnect from oracle;
%put Connection failed after &max_connection_attempts attempts.;