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;
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
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
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
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
Thank you Jim I'll try this macro!
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...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: