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.
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
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)
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.