topic Re: Error Notification in SAS Programming

Error Notification

Linda_V — Mon, 07 Dec 2020 14:25:27 GMT

I have code in one of my cron jobs to send a notification to my email of any errors during the morning execution.

If the program ran without errors, I send a message that it completed. If not, it lists the completion code.

Yesterday's run did contain an ERROR due to an empty table during a SQL step. However, my code did not capture this error? Any suggestions?

Current code:

%macro send_mail;
filename mymail email '<email address>' subject='STATUS Code in CRON job';
%if &syscc>0 %then %do;
data _null_;
file mymail;
put "Checking on the STATUS CODE of the daily cron jobs on &sysdate. at &systime.";
put "STATUS code = &syscc. 0=success, 4=warning";
run;
%end;
%else %do;
data _null_;
file mymail;
put 'The job ran to completion';
run;
%end;
%mend;
%send_mail

Error in log yesterday that my code did not pick up:

ERROR: Table <LIBNAME.TABLE_NAME> doesn't have any columns. PROC SQL requires each of its tables to have at least 1 column.

Thank you.

Re: Error Notification

Kurt_Bremser — Mon, 07 Dec 2020 15:27:17 GMT

There is something else at work here.

See this code:

%let syscc=0;

data class;
set sashelp.class;
drop _all_;
run;

proc sql;
select * from class;
quit;

%put &syscc;

%if &syscc>0 %then %do;
%put "error detected";
%end;

which causes your ERROR message. SYSCC is set to 1012, and that is detected in a %IF:

 73         %let syscc=0;
 74         
 75         data class;
 76         set sashelp.class;
 77         drop _all_;
 78         run;
 
 NOTE: There were 19 observations read from the data set SASHELP.CLASS.
 NOTE: The data set WORK.CLASS has 19 observations and 0 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 79         
 80         proc sql;
 81         select * from class;
 ERROR: Table WORK.CLASS doesn't have any columns. PROC SQL requires each of its tables to have at least 1 column.
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 82         quit;
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE:  Verwendet wurde: PROZEDUR SQL - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 83         
 84         %put &syscc;
 1012
 85         
 86         %if &syscc>0 %then %do;
 87         %put "error detected";
 "error detected"
 88         %end;
 89

Re: Error Notification

ballardw — Mon, 07 Dec 2020 17:44:48 GMT

What does the code look like immediately before you call the Send_mail macro?

Suspect something else is called that is resetting your &syscc

It has been a long time since I have used the error codes but found it useful to copy the macro variable values to a specific macro variable at each point it was likely to be an error. Then I had a list of macro variables and showed status of all the steps not just the job overall (last step that sets the macro value)