errorabend vs runquit?

[agrocrag14]

I have a step in my code that clears a table in oracle, then writes data to that table(step 1). The next data step (step 2)inserts into another table the counts of records I have pushed into oracle (in the prior data step). A potential problem I have encountedred is in the instance of a rollback error (oracle error is that there is a key constraint issue), step 1 stops but step 2 completes. I want to make sure that in this instance if step 1 fails, so does step 2.

summary:

proc sql;

delete from ORACLE.table; quit; 

/*STEP 1*/

data ORACLE.table; 

set sas.mydata;

run;

(potential for error: rollback occurs)

/*STEP 2*/

proc sql;

create table load_stats as

select distinct group, count(data)

'Y' as status

from sas.mydata

group by name;

quit;

data ORACLE.table2;

set load_stats;

run;

[jimbarbour]

What version of SAS EG are you running?  If you're running 5.1 or later, I find that issuing an ABORT CANCEL command works really well any time an error is detected.  If you're running 4.3 or earlier, don't use the ABORT CANCEL; it will hang your SAS EG session, and you'll have to kill the session.

What I typically do is write a little macro and then execute that macro after I do a step.  The macro checks &SYSERR, &SYSFILERC, &SQLRC, &SQLXRC, or &SYSLIBRC as appropriate.  If an error is detected, I issue %ABORT CANCEL in my macro, and all subsequent steps in that program are cancelled.

Jim

[jimbarbour]

Here's a sample of one of the macros I've written.  I won't claim that it's pretty, but it works.  You can comment out a lot of the "PUT" statements that write to the log.  Sometimes I use them; sometimes I don't depending on whether I'm doing test or prod work.

/*-------------------------------------------------------------------------------------------------*/
/*  Macro:    Check_SYSERR
/*	Author:   Jim Barbour 
/*	Date:     19 January 2016
/*  Run Time: Under 1 minute.
/*	Remarks:  This macro checks macro variable SYSERR and sets global macro variable ReturnCode to 8
/*			  and aborts the run if SYSERR is non-zero.  
/*
/*			  ReturnCode values are as follows:
/*				ReturnCode = 00 -- No errors or warnings.
/*				ReturnCode = 02 -- Note (less than an error or warning).
/*				ReturnCode = 04 -- Warning (less than an error).
/*				ReturnCode = 08 -- Error.
/*-------------------------------------------------------------------------------------------------*/


%MACRO Check_SYSERR  /  STORE;
	;  RUN;  QUIT;
	%IF  &Control_Value		 	=	CANCEL  %THEN
		%DO;
			%LET  ReturnCode  	=	8;
		%END;
	%ELSE
		%DO;
			%PUT  NOTE- ;
			%PUT  NOTE:  Check_SYSERR;
			%*PUT  NOTE-  ReturnCode  =  &ReturnCode;
			%PUT  NOTE-  SYSERR      =  &SYSERR;
			%PUT  NOTE-  SYSFILRC    =  &SYSFILRC;
			%PUT  NOTE-  SYSLIBRC    =  &SYSLIBRC;
			%IF  &SYSERR  ^=  0  %THEN
				%DO;
					%LET  ReturnCode  =  8;
					%PUT  NOTE- ;
					%PUT  WARNING:  Cancelling run due to high error code;
					%ABORT  CANCEL;		/*  ABORT CANCEL is not supported in SAS Enterprise Guide 4.3 and prior.  */
					%LET  Control_Value	=	CANCEL;
				%END;
		%END;
%MEND  Check_SYSERR;


/*  Sample code:
%LET  Control_Value	=	;
%LET  ReturnCode  	=	0;
	and
%INCLUDE	"/home/barbourj/macros/Check_SYSERR.sas";
%Check_SYSERR;
*/

The ReturnCode and Control_Value variables are something that I use to pass values between programs in SAS EG; you probably can omit them.

The %INCLUDE example is not needed if you use the / STORE option and create a compiled macro.

Jim

[jimbarbour · Accepted solution]

I don't know if this might be helpful or not, but here's a quick example.  Say I have this (clearly non-functional) SAS SQL code:

%INCLUDE	"/home/c10757a/macros/Check_SQLRC.sas";

PROC  SQL;
	CREATE TABLE  Imaginary_Table	AS
	SELECT *	
	FROM  Does_Not_Exist
	;
QUIT;
RUN;
%Check_SQLRC;

Notice that 1) I precede the SQL with a %INCLUDE to bring in my utility macro and 2) that I follow the SQL with a call to the macro, %Check_SQLRC.

Here's my log after execution:

85         PROC  SQL;
86         	CREATE TABLE  Imaginary_Table	AS
87         	SELECT *	
88         	FROM  Does_Not_Exist
89         	;
ERROR: File WORK.DOES_NOT_EXIST.DATA does not exist.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
90         QUIT;
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
      
91         RUN;
92         %Check_SQLRC;
     
NOTE:  Check_SQLRC
       SQLRC       =  8
       SYSERR      =  8
       SYSFILRC    =  1
       SYSLIBRC    =  0
     
WARNING:  Cancelling run due to high SQL return code

Notice that there is a note "Check_SQLRC", which lets me know my macro has been called, and then a listing of the return and error codes that my macro encountered.  Note that &SQLRC = 8 (an error).  The macro then executes a %ABORT CANCEL stopping any subsequent steps from executing. 

Hopefully that all makes sense.  I've been using it or a variant for a number of years, and it has served me well.

Jim

[agrocrag14]

Thank you Jim I'll try this macro!

[RW9]

Not sure I am following your reasoning here.

proc sql;
  delete from ORACLE.TABLE; 
  insert into ORACLE.TABLE
  select * from SAS.MYTABLE;
  create table ORACLE.MYTABLE2 as
  select   distinct 
           GROUP,
           count(DATA),
           'Y' as status
   from     SAS.MYDATA
   group by NAME;
quit; 

The above coce is after cleaning up the code, and removing the typo's (e.g. missing comma after count(DATA)).

It seems that you want to create two tables on the database, one is a summarised version of the other.  Now generally speaking, you wouldn't need the summarised table at all, as that can be created on the fily (or use a view onto the real data).

So several questions seem to jump to mind:

- Why does this data need to get uploaded to the database, removing what is already there?  Should really be the other way round to my mind, the data is stored in the Base and extracted to SAS.

- Why do you need a table with the summarised information, create a view.

- Why do you need the second part to not come into effect is a problem?  If you want to do this then maybe switching over to Oracle SQL editor, or Toad or some of those Tools would be better as they specifically handle Oracle errors.   However if the second table is actually a view...

[agrocrag14]

RW9 you are missing the point of my question, Jim has already answered it. Has nothing to do with my code, which was only typed to illustrate the written explanation as a visual.