Not send email if error exist

[Abraham]

Hello Expert.

My requirement is to display error message in the template of email along with total individual observation for dataset one and two.

If there is an error in the program, it should not transmit output  to request user (abc@gmail.com).

In stead, it will send email with log file to programmer "bcd@gmail.com" with list of error message in the template.

Email Template should be like below

Please see attached file for your daily report

Total number of observation for dataset one=5

Total number of observation for dataset one=19

List of error

ERROR:- merging issue       

Total warning message :-

Can anybody help me how to create template like below from below sample dataset.

Thanks in advance

Program

%let run=&sysdate;
proc printto
log="/C:/Log/&run._ONE..log" New;
run;

data one;
INPUT NAME $ SEX $ AGE Height WEIGHT;
datalines;
STEVE M 41 74 170
ROCKY M 42 68 166
KURT M 39 72 167
DEBORAH F 30 66 124
JACQUEL F 33 66 115
;
run;

data two;
set sashelp.class;run;

data three;
set one two;
run;

%let OutPut=/C:/test/output/;
ods tagsets.excelxp FILE = "&Output/Test_&run..xls";
proc print data= three ;
title "Dataset three Output" ;
run;
ods tagsets.excelxp close;

proc printto;run;
*Begin email code;
FILENAME myemail2 EMAIL from=("abch@gmail.com")
to=("abc@gmail.com")
Subject = "An email with attachment sent from SAS"
Attach = ("/C:/Output/Test_&run..xls"
"/C:/log/&run._TEST..log");

data _null_;
file myemail2;
/ /put "Please see attached file for your daily report."
/ / "Thank you and have a great day."
/ / " "
/ /"Sincerely";
run;

[Shmuel]

Use next skeleton in your program:

%let SYscc=0;

    ==== your code enter here without any email code ====

 %let myrc = &syscc;

%macro check;

      %if %eval(&myrc) LE 4 %then %do;

            ==== enter code for email when no errors ====

      %end;

     %else %do;

           ==== enter code for email when error occured ====

    %end;

%mend check;

%check;

[Kurt_Bremser]

I'd rather use &syscc, as &sysrc holds the return code of the last system command, not the state of the SAS session.

[Shmuel]

@Kurt_Bremser, I agree with you. It was my typo.

[Abraham]

Thank you very much for your support.

How can I display the error message in the email template. 

Along with this, I want to count the total warning message to display on the template.

Thanks once again

[Shmuel]

To select the error messages / count warning messages - you will need to save the log in file,

read it, locate the messages (use index or find function) and save them in a flat file to be attached to the email.

A new skeleton should look like:

%let SYscc=0;
filename mylog '...any name.log';
proc printto file=mylog; run;  /* open and start keep log */

    ==== your code enter here without any email code ====
%let myrc = &syscc;
%proc printto; run;   /* close the log file */

/* checking the log */
filename msg '...any name.txt';
data null;
  retain count_warn 0;
   infile mylog  end=eov;
   file msg;
   input a_line $200;  /* adapt to max log line */
   if index(a_line, 'ERROR') then put a_line;
   if index(a_line,'Warning')  then count_warn +1;
   if eov then put 'WARNING ' count_warn=;
run;
   

%macro check;
      %if %eval(&myrc) LE 4 %then %do;
            ==== enter code for email when no errors ====
      %end;
     %else %do;
           ==== enter code for email when error occured ====
              === with attached file MSG ===
    %end;
%mend check;
%check;

You may prefer check &myrc to 0, then even one warning will treat it as having ERROR, or

you may send both emails when &myrc=4, as there are only warnings.

[Abraham]

Thank you very much Shumel for your great help.

I tried to implement that changes which you suggested. However, I donot know how to display it in the email template.

Also request to check the code once again.

Thanks once again in advane for your support

Program

%let run=&sysdate;
%let SYscc=0;
filename mylog "C:/&run._jacob..log";
proc printto file=mylog; run; /* open and start keep log */

data one;
input NAME $ SEX $ AGE Height WEIGHT;
datalines;
STEVE M 41 74 170
ROCKY M 42 68 166
KURT M 39 72 167
DEBORAH F 30 66 124
JACQUELI F 33 66 115
;
run;

%let OutPut=C:/;
ods tagsets.excelxp FILE = "&Output/Test_&run..xls"
style=analysis
options (embedded_titles='yes'
embedded_footnotes='no'
orientation='landscape'
fittopage='yes'
missing_align='center'
autofit_height='yes'
autofilter='yes'
row_repeat='1'
sheet_name='Data'
sheet_interval='None');;
proc print data= one ;
title "jacob Output" ;

run;
ods tagsets.excelxp close;

%let myrc = &syscc;
proc printto; run; /* close the log file */

/* checking the log */
filename msg 'C:/help.txt';

data _null_;
retain count_warn 0;
infile mylog end=eov;
file msg;
input a_line $200; /* adapt to max log line */
if index(a_line, 'ERROR') then put a_line;
if index(a_line,'Warning') then count_warn +1;
if eov then put 'WARNING ' count_warn=;
run;

*Begin email code;
%let myrc = &syscc;
%macro check;
%if %eval(&myrc) LE 4 %then %do;
FILENAME myemail2 EMAIL from=("abc@gmail.com")
to=("abc@gmail.com")
Subject = "Email without any error"
Attach = ("C:/Test_&run..xls"
"C:/&run._jacob..log");

data _null_;
file myemail2;
Put "Dear X"
/ /put " "
/ /put "Please see attached log file for your daily report with no error."
/ / "Thank you and have a great day.";
run;
%end;
%else %do;
FILENAME myemail2 EMAIL from=("abc@gmail.com")
to=("abc@gmail.com")
Subject = "Email with error"
Attach = ("C:/&run._jacob..log");

data _null_;
file myemail2;
Put "Dear X"
/ /put " "
/ /put "Please see attached log file for your daily report with error."
/ / "Thank you and have a great day.";
run;
%end;
%mend check;
%check;

[Shmuel]

Please see my remarks in attached msword documentation.

If you have issues with the output mail, please attach the log and the mail you got.

[Abraham]

Thank you Shmuel once again for your great support. Based on your suggestion, I have rectified the program and it is working fine.

As you asked whether to attach the whole log file or only the warnings during email alert, I need both attached log file and warning/error message in the email template. If not possible, at lease warning and error should be displayed in the body of email like below

Subject = Email with error
Dear X"
Please see log file for your daily report with error."

<I need errror and warning message to be available here e.g.>

ERROR: ORACLE connection error: ORA-12154: TNS:could not resolve the connect identifier specified.

Total number of warning message=6

Thank you and have a great day.

I attach the log file for reference. In the program, I have added connect by procedure to access one table to get ORA- error so that I can explain my requirement.

Thank you in adance

[Shmuel]

I have addapted the checking log code to create 2 macro variables -

    (1)  ERR_LINE    - is the ERROR message and

    (2)  WARN_LINE - is the count of warnings.

You can include those macro variables in the body of your letter:

/* checking the log */
***filename msg 'C:/help.txt';   /* use macro vars instead the msg file */
data _null_;
retain count_warn 0;
infile mylog end=eov;
***file msg;
input a_line $200;    /* adapt to max log line */
if index(a_line, 'ERROR') then
   call symout('ERR_LINE, trim(a_line));
if index(a_line,'Warning') then count_warn +1;
if eov then 
   call symput('Warn_line ', cat('there are ',left(count_warn), '  warnings');
run;

Maybe you will need declare those macro variables as %global.

[Abraham]

Thanks a million Shmuel for your support.

One query , When I use the macro variable in the body of email, why it is not resolved. Is it like that I made some mistake in the program. Attaching it for reference.

Along with this, Why the log file only show repetition of output after changing the program, not the fully generated one from text.

May I request you to guide.

[Shmuel]

Out statements accepts either a variable name or a literal.

Change code:

put  &warn_line.;
put  &err_line.;

into:

put  "&warn_line.";
put  "&err_line.";

[Shmuel]

The file log.docx doesn't seem to show the log of the program.

I couldn't find any put statemants or a loop to create it.

Try to locate what creates this output.

Maybe you have overwitten the log with some output reporting ?

[Abraham]

The error and warning message resolve to blank even though they are available in the program.

I think there is an issue with proc printto and filename statement because when I check the log file, it contains only attached output with out any warning/error message. Therefore, the macro variable resolve it to blank. (attached log file for your reference)

 filename mylog "/proj/sastmp/pvdm/99_TEST_QC/Test/ANA_028/&run._ANA_028.log";
proc printto file=mylog new; run;

[Kurt_Bremser]

You need to execute that code at the beginning of your program; only subsequent code will put its log there, code that has already run won't show.