You should check for error return codes in any SAS programs that run unattended in batch jobs, so you can handle the exception properly. For example, if the data are invalid, you don’t want to generate reports or insert bad data into a database. Also it can save time to abort as soon as the error is detected.
Programming languages like Java and Python will often throw an exception which must be handled explicitly, and if it is not handled, then the program will stop. SAS, on the other hand, will blindly continue executing commands after an error.
The solution is to wrap the entire SAS program in a macro. After each SAS command (DATA step, PROC SQL, PROC SORT, etc.) check for an error code. For PROC SQL, I check &SQLRC; for everything else, I check &SYSERR. If the return code is non-zero, I jump to the end. In some cases, I print an error message to the SAS log.
In the following example are three procedures, and each contains an error. If you run at is, the second two will not run. Fix the first error, and run again: this time the last procedure will not run.
%macro sortclass; proc sql; create table class as select * from sashelp.classs /* typo */ ; quit; %if &amp;SQLRC gt 0 %then %do; %put ERROR: PROC SQL failed, so I will not continue.; %goto exit; %end; data class; set class; format bmi 10.1.; /* fix: remove the period at the end */ bmi = weight/height; run; %if &amp;SYSERR gt 0 %then %goto exit; proc sort data=class; by doesnotexist; /* fix: change 'doesnotexist' to 'bmi' */ run; %if &amp;SYSERR gt 0 %then %goto exit; %exit: %mend; %sortclass;
If you execute multiple commands (SELECT, INSERT, DROP, etc.) in one PROC SQL, then check &SQLRC after each command.
Instead of going to one exit point, you may have two jump labels:
error. In the
error label, you can send an email asking a human to check the SAS logs. Example:
%macro sortclass; proc sql; create table class as select * from sashelp.classs /* fix: remove the extra 's' */ ; quit; %if &amp;SQLRC gt 0 %then %goto error; %goto exit; /* there is no error */ %error: filename mymail email &quot;firstname.lastname@example.org&quot; subject=&quot;error in SAS program&quot;; data _null_; file mymail; put 'check the logs'; run; %exit: %mend; %sortclass;
One unpleasant side effect of wrapping the program in a macro is SAS 9.2 (at least on Windows) loses syntax highlighting for the non-macro bits such as PROC SQL and DATA steps.
Checking return codes is one technique that is part of a larger strategy of error checking. For example, you can add automated checks that that a data set has any or enough observations, that duplicates keys do not exist, that the values are not missing, that numerics are within normal ranges, etc. If you have a macro that attaches variables to an arbitrary input data set, then the input data set should not already have the same variable that will be attached: this can lead to unexpected results. And depending on your situation, there are many other things that can be checked.
Update (March 2016): See the new post In case of error in SAS program, send email and stop, which focuses on a lighter-weight approach that does not lose syntax hightlighting.
7 thoughts on “Checking return codes for errors in SAS”
Its Nice! need to test
Would that work on a remote session or do we have to run it locally?
I use this method in local, interactive SAS session and batch mode. Try the sample code in your environment, and let me know how it goes.
I tested the sample (with some modifications) and it works really well! The macro was ran in local with SAS (computer license). Unfortunately, it seems that it does not work with submitting some block codes on the remote server (rsubmit /endrsubmit).
However, the macro is very very useful and it can be adapted easily.
Thanks for this tip Andrew 🙂
Technically you could just check &SQLRC >= 4 if you wanted it to run despite just a warning popping up.
Does the &SQLRC work within a macro which has prc sql in it?
Shubha: Yes, &SQLRC works after any SQL statement within macros and outside of macros.