How to use SYSERR in macro as e-mail trigger if error during query to Teradata

[NiespielakWojc]

Hello,

Long story short. In my company is a migration from old server to new one. Good oportunity to deepedive into old code and make some upgrades. So i try do some easy notyfication about errors during calculation and I have some problems.

Idea is as following.

Add to every sas code scheduled on server one macro execution line

%codeEndStatus(e-mail title,&SYSERR.);

This leads to macro program

%macro codeEndStatus(codeName,error);
%include "&commonsPath./emailAuth.sas"; /*data necessary to send e-mails*/

%if &error. = 0 %then %do ;
OPTIONS LINESIZE=256;
FILENAME output EMAIL SUBJECT= "#OK &codeName. OK"
FROM= e@mail.com
TO=e@mail.com
attach=("&logSpace./&curentProgramName._&todayShort..log")
CT= "text/html";* Required for HTML output;

ODS HTML BODY=output STYLE=noBorder;
proc odstext;
p "Everything is fine";
run;
ODS HTML CLOSE;

%end;
%else %do;
OPTIONS LINESIZE=256;
FILENAME output EMAIL SUBJECT= "#Error &codeName. "
FROM= e@mail.com
TO= e@mail.com
attach=("&logSpace./&curentProgramName._&todayShort..log")
CT= "text/html";* Required for HTML output;

ODS HTML BODY=output STYLE=noBorder;
proc odstext;
p "Something went wrong, check log file";
run;
ODS HTML CLOSE;

%end;

%mend;

And. Everything is fine except this situation (most common in my work).

If errors occur during selecting data from Teradata or Hadoop dataset, from SAS point of view It`s no error. Everything fine. Naturally output file is empty.

Yes, usualy empty file provide errors during next computing. But not in my case, becouse I select only recent record from Teradata and append it with existing in SAS serwer dataset.

So I looking for a solution. Maybe it`s another variable to use?

First thing I invented it`s simply check number of record in new table and stop executing if it's null. But I have a lot of query to external sources in my code, so adding few lines after every query is a monkey job.

[Kurt_Bremser]

Have you tried using &syscc instead of &syserr?

[noling]

I was going to recommend that you check that the tables are not empty, but it sounds like you've already considered that. 

Do you call your %codeEndStatus macro after each table you query? If you're only considered about a small number of the tables being empty, you could add an additional parameter indicating that you should check the number of records. You could do this inside your %codeEndStatus macro, and set the default to false so that you don't have to change your logic for all your other jobs. Is my understanding correct? 

[NiespielakWojc]

I call macro at the end of code. So after every query.

[Kurt_Bremser]

PS it is easier to have the scheduler react to the overall return code of a SAS program and send mails.