topic Re: DI Studio - Job's Status handling modified code doesn't support email variable in SAS Data Management

DI Studio - Job's Status handling modified code doesn't support email variable

pope1970 — Mon, 14 Jul 2014 06:09:51 GMT

I slightly modified the default status code for Job and copy/paste in Job Properties - PostCode.

So, the code work if succesfull => &job_rc eq 0

and if I have hard coded email address

(address = tod.smith@.....com,

Message = ------ Syntax not correct - Error);

But if I have error => &job_rc eq 3

but if I have variable for email address then it doesn't work...

(address = &&email1,

Message = ------ Syntax not correct - Error);

Here is the original code

%macro etls_jobRCChk;

%if (&job_rc eq 3) %then

%do;

%macro etls_sendEmail(address=, message=);

proc sql noprint;

create table error_detail as

select strip(t2.recipient) as recipient

from RISK.ERROR_CONTROL t2

where t2.jobflow = 'DM_LOAD' and

t2.subject = 'Syntax Error';

quit;

proc sql noprint;

select recipient

into :email1

from error_detail;

quit;

filename sendMail email "&address" subject='Load_ALL_STAGING - Job Load_STG_MTM_AUDIT --- Error';

%local etls_syntaxcheck;

%let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck));

options nosyntaxcheck;

data _null_;

file sendMail;

dttm = put(datetime(),nldatm.);

put dttm "&message.";

run;

options &etls_syntaxcheck;

%mend etls_sendEmail;

%etls_sendEmail

(address = &&email1,

Message = ------ Syntax not correct - Error);

%end;

%mend etls_jobRCChk;

%etls_jobRCChk;

Re: DI Studio - Job's Status handling modified code doesn't support email variable

Quentin — Mon, 14 Jul 2014 11:13:58 GMT

Hi,

Can you add options MPRINT at the top, and then show the log you get which includes the error message?

It looks like you are trying to pass macro variable &email1 in the macro call, but that macro variable hasn't been created yet! So maybe just skipping that will help.

Below suggest some style changes (particularly to avoid defining a macro inside another macro definition) and added debugging as well:

options mprint;

%macro etls_sendEmail(message=);

%local email1;

proc sql noprint;

select strip(t2.recipient) into: email1

from RISK.ERROR_CONTROL t2

where t2.jobflow = 'DM_LOAD' and

t2.subject = 'Syntax Error';

quit;

%*above will return only one value from email1, so if could have multiple email addresses, would need to add separated by() above and pass list of email addresses to below;

%put email1=&email1;

filename sendMail email "&email1" subject='Load_ALL_STAGING - Job Load_STG_MTM_AUDIT --- Error';

%local etls_syntaxcheck;

%let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck));

options nosyntaxcheck;

data _null_;

file sendMail;

dttm = put(datetime(),nldatm.);

put dttm "&message.";

run;

options &etls_syntaxcheck;

%mend etls_sendEmail;

%macro etls_jobRCChk;

%if (&job_rc eq 3) %then

%do;

%etls_sendEmail

(Message = ------ Syntax not correct - Error)

%end;

%mend etls_jobRCChk;

%etls_jobRCChk;

Re: DI Studio - Job's Status handling modified code doesn't support email variable

pope1970 — Mon, 14 Jul 2014 15:50:55 GMT

Hi Quentin

Your answer helped me to identify error and the error is

ERROR: Email address ddddd.ccccc@rrrrrrrr.com

too long. 256 character limit.

NOTE: The SAS System stopped processing this step because of errors.