topic Re: Not send email if error exist in SAS Procedures

Not send email if error exist

Abraham — Sun, 09 Jul 2017 07:34:22 GMT

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;

Re: Not send email if error exist

Shmuel — Sun, 09 Jul 2017 14:59:32 GMT

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;

Re: Not send email if error exist

Kurt_Bremser — Sun, 09 Jul 2017 14:17:54 GMT

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

Re: Not send email if error exist

Shmuel — Sun, 09 Jul 2017 14:39:28 GMT

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

Re: Not send email if error exist

Abraham — Sun, 09 Jul 2017 17:16:27 GMT

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

Re: Not send email if error exist

Shmuel — Sun, 09 Jul 2017 17:46:01 GMT

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.

Re: Not send email if error exist

Abraham — Tue, 11 Jul 2017 16:02:54 GMT

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;

Re: Not send email if error exist

Shmuel — Tue, 11 Jul 2017 17:02:30 GMT

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.

Re: Not send email if error exist

Abraham — Wed, 12 Jul 2017 17:42:39 GMT

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

Re: Not send email if error exist

Shmuel — Wed, 12 Jul 2017 19:45:24 GMT

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.

Re: Not send email if error exist

Abraham — Thu, 13 Jul 2017 09:22:29 GMT

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.

Re: Not send email if error exist

Shmuel — Thu, 13 Jul 2017 10:07:39 GMT

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.";

Re: Not send email if error exist

Shmuel — Thu, 13 Jul 2017 10:18:51 GMT

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 ?

Re: Not send email if error exist

Abraham — Thu, 13 Jul 2017 11:47:51 GMT

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;

Re: Not send email if error exist

Kurt_Bremser — Thu, 13 Jul 2017 11:51:45 GMT

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.

Re: Not send email if error exist

Shmuel — Thu, 13 Jul 2017 11:58:26 GMT

Please attach your code using the {i} icon. It will be easier to read and to locate errors.

Attaching a .txt file is not very friendly and less usefull.

Re: Not send email if error exist

Abraham — Thu, 13 Jul 2017 12:51:58 GMT

%let run=&sysdate;
%let SYscc=0;
filename mylog "/C:/&run._test.log";
proc printto file=mylog new; 
run; /* open and start keep log */
%global ERR_LINE WARN_LINE;
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;
/*added coded only to test error and warning msg*/
proc sql;
        connect to oracle as EXT1 (user="&User." orapw="&OraPw." path="&Path.");
            create table work.abc as
                Select * from connection to EXT1
(select * from emp
);
disconnect from ext1;
quit;

%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 "test Output" ;
run;
ods tagsets.excelxp close;			 
 		 
%let myrc = &syscc;
proc printto log=log; run; /* close the log file */
/* checking the log */
*filename msg '/proj/sastmp/pvdm/99_TEST_QC/help.txt';
data _null_;
retain count_warn 0;
infile mylog end=eov;
***file msg;
input a_line $2000;    /* adapt to max log line */
if index(a_line, 'ERROR') then 
   call symput('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;
*Begin email code;
%put "&warn_line.";
%put "&err_line.";
%macro check;
%if %eval(&myrc) LE 4 %then %do;
FILENAME myemail2 EMAIL from=("abc@gmail.com")
 to=("abc@gmail.com")
 Subject = "SAS without any error"
  Attach = ("/C:/Test_&run..xls"
			"/C:/&run._test.log");
data _null_;
file myemail2;
Put "Dear X";
put "  ";
put "Report with no error";
run; 
%end;
%else %do;
 FILENAME myemail2 EMAIL from=("abc@gmail.com")
 to=("abc@gmail.com")
 Subject = "SAS with error"
 Attach = ("/C:/&run._test.log");
data _null_;
file myemail2;
Put "Dear X";
put  "&warn_line.";
put  "&err_line.";
put "Report with error.";
run; 
%end;
%mend check;
%check;

Please have a look .

Re: Not send email if error exist

Shmuel — Thu, 13 Jul 2017 13:30:30 GMT

1) Change line:

proc printto log=log; run; /* close the log file */

into

proc printto; run; /* close the log file */

2) Checking the log:
As there is a check for strings "ERROR" and "Warning" - I hope you may have them in log, given by sas only.

I hope that will finally give you the desired output.

Re: Not send email if error exist

Abraham — Fri, 14 Jul 2017 07:36:15 GMT

Thanks Shmuel for your great help and guidance. The issue is still there.

I will try with some other program.