BookmarkSubscribeRSS Feed
Linda_V
Obsidian | Level 7

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.

2 REPLIES 2
Kurt_Bremser
Super User

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         
ballardw
Super User

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 700 views
  • 0 likes
  • 3 in conversation