Did you catch that SAS error?
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.
data new_ds;
set old_ds;
put something here;
run;
%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.
Let SAS check for a database connection
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.
%macro check_db_connect(max_connection_attempts,connect_to_string,time_to_sleep);
%local sleep_statement connected_to_database;
/*--------------------------------------------------------------------
Program Name : check_db_connect_ah.sas
Purpose : checks database connection
Author : HoodA (www.AFHood.com)
Arguments
You may pass the values in via the macro parameters or by setting the
values within the macro itself.Requirements
Only checks one database connection. Must run multiple times for each
database connection you want to check.Returned values
This macro will return a variable, "database_connection" with
a value of YES or NO;===================================================================
Change History2009.03.01 - hooda - Macro created
---------------------------------------------------------------------*/
/* Settings */
%* USE THESE SETTINGS IF YOU DONT PASS THE VARIABLES INTO THE MACRO
VIA THE MACRO PARAMETERS ;
%let max_connection_attempts_default=10;
%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 */%put &connect_to_string_default;
%put &connect_to_string;
%put %length(&connect_to_string);
/* Settings */%* checking for passed parameters ;
%if (&max_connection_attempts ="" or &max_connection_attempts le 1 ) %then %do;
%let max_connection_attempts=&max_connection_attempts_default;
%end;
%if ( %length(&connect_to_string) < 5 ) %then %do;
%let connect_to_string=&connect_to_string_default;
%end;
%if ( &time_to_sleep le 1 ) %then %do;
%let time_to_sleep=&time_to_sleep_default;
%end;%* this is the actual sleep statement ;
%let sleep_statement=%eval(&time_to_sleep*60);%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.;
%put ;%let connected_to_database=NO;
%* attempt to connect up to x times at x minute intervals,;
%* until successful or out of time;
%do i = 1 %to &max_connection_attempts;
proc sql;
connect to &connect_to_string;
%* if the query succeeded, exit the loop ;
%if ( &SQLXRC=0 ) %then %do;
%let connected_to_database=YES;
%if ( &i = 1 ) %then %do;
%put Connection succeeded after 1 attempt.;
%end;
%else %do;
%put Connection succeeded after &i attempts.;
%end;
%let i=&max_connection_attempts;
%end;
%else %do;
%* if the connection failed, wait 10 minutes and try again;
quit;
filename sleepout pipe "sleep &sleep_statement";
data sleepout;
infile sleepout;
run;
%end;
%end;
%* 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. ;
%global database_connection;
%if &connected_to_database=YES %then %do;
disconnect from oracle;
quit;
%let database_connection=YES;
%end;
%else %do;
%put Connection failed after &max_connection_attempts attempts.;
%let database_connection=NO;
%end;
%mend check_db_connect;