CODEWITHSUNDEEP
error-handlingsassas-macroproc-sql
ch1nmay
ch1nmay

Reputation: 81

Error Handling in a sas macro

I am writing a simple macro to count distinct values in all the columns of a table. I need to include an error handler which displays an error information and conitnues to execute the macro further, if certain column is found in the first but not in the second table.

for eg, lets say, i write a macro to count the distinct values for col1, col2, col3 in a any dataset, and

table1 has columns (col1, col2, col3) but, table2 has columns (col2, col3) - hence there will be an error that col1 doesnot exists in table2. i need a way to handle this error.

Upvotes: 3

Views: 9247

Answers (1)

Robert Penridge
Robert Penridge

Reputation: 8513

Love SAS, but I hate error handling in it (as it's near non-existent and almost always needs to be done using macro code... yuck).

Your best bet is to check for any conditions prior to executing the code, and if any requirements are not met then some of your options are:

  • Abort with a descriptive message prior to the step being run. I find that the %abort cancel statement is the nicest way to stop code in both batch and interactive sessions.
  • Skip the step that would fail using a %if %then statement.
  • Have the code gracefully fix the issue and continue (if that's a possibility). This could be done by conditionally running additional code that's not normally part of the regular job flow.
  • Have it run and then reset the error condition (ie. set &syserr to zero)? I've never really played around with this option so I'm not 100% sure how it works of even if it's feasible.

In your case I imagine your code will look something like:

data have1;
  set sashelp.class;
run;

data have2;
  set sashelp.class(drop=age);
run;

/* GET A LIST OF COLUMNS IN EACH TABLE */
proc sql noprint;
  create table column_list as
  select memname, name
  from dictionary.columns
  where libname = 'WORK'
   and memname in ('HAVE1','HAVE2')
  order by name
  ;
quit;

/* CREATE A DATASET CONTAINING COLUMNS THAT ONLY EXISTS IN ONE OF THE TWO TABLES */
/* YOUR LOGIC MAY DIFFER */
data diff_columns;
  set column_list;
  by name;
  if first.name and last.name then do;
    output;
  end;
run;

%macro error_handling;
  %if %nobs(iDs=diff_columns) %then %do;  
    %put ERROR: TABLES CONTAINED DIFFERENT COLUMNS.;
    /* CHOOSE HOW YOU WANT TO HANDLE IT HERE */
  %end;
%mend;
%error_handling;

A few things... I've used a macro called %nobs() to help me determine if there are any obersvations in the diff_columns dataset. There are many different versions of %nobs, here is a selection.

If you decide you want to have SAS end without running any more code, a good macro for doing that is shown below. It will quit SAS if running in batch mode, but if you're running interactively it will just cancel the remaining submitted code without leaving SAS:

%macro stop_sas;
  %if "&sysenv" eq "FORE" %then %do;
    %abort cancel;
  %end;
  %else %do;
    endsas;
  %end;
%mend;

If you want to stop SAS from cluttering up the log once it has encountered any errors, consider using the %runquit macro. It looks like this, and usage instructions can be found here:

%macro runquit;
  ; run; quit;
  %if &syserr %then %abort cancel;
%mend;

Error handling in SAS is a pretty messy business and while this gives you somewhere to stat I'm sure this list is by no means comprehensive. I'd suggest just try a few different approaches that I've listed above and then choose whatever worked best for you...

Upvotes: 3

Related Questions