Enabling Errorchecking for multiple procsql in sas code

Question

I have SAS code, within a macro i have 3 proc sql code which gets executed on sequence. I want to enable rc check, so that any of the proc sql throws an error other code shouldn't get executed. Please let me know the possible way to do it within sas code itslef.

Answer 1

Use the syserr automatic macro variable and a conditional abort after each sql block. The macro below will not execute the third sql block because the second sql block contains an error.

%macro foo;
    proc sql;
        create table one as
            select *
            from sashelp.cars;
    quit;
   
    %if(&syserr. > 6) %then %abort;

    proc sql;
        create table two as
            select *
            from table_does_not_exist;
    quit;
   
    %if(&syserr. > 6) %then %abort;

    proc sql;
        create table three as
            select *
            from sashelp.class;
    quit;
%mend;

%foo;

Log:

NOTE: Table WORK.ONE created, with 428 rows and 15 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      
ERROR: File WORK.TABLE_DOES_NOT_EXIST.DATA does not exist.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
ERROR: Execution terminated by an %ABORT statement.