Conditional E-mail Report

jdmarshg — Fri, 30 Oct 2015 22:16:31 GMT

Hello everyone,

I'm looking to achieve a conditional e-mail based on if the job_rc <=4 in bold and place the italic code within the logic.

So far I'm receiving many errors due to the end; being out of proper order.

Can proc odstext and proc report be placed within the logic?

Looking for guidance. I only want it to sent a report if the job return code is 4 or below.

data _null_;
if (&job_rc <= 4) then
do;
FILE Mailbox TO='<Jonathan.Marshall@treasury.gov>'
Subject="&dataset. datafeed &mailJobStatus."
Attach="/data/ETL_work/&Environment/Reports/&dataset..xls";
PUT "Dataset &dataset. has been successfully loaded into the %sysfunc(strip(&datasetName.)) database or into SAS datasets on %sysfunc(strip(&dbserverName.)).";
PUT "Please see attached spreadsheet with results";
end;
Else ;
if (&job_rc > 4) then
do;
FILE Mailbox TO='<Jonathan.Marshall@treasury.gov>'

Subject="&dataset. datafeed JobStatus";
end;
run;

LIBNAME ETLAUDIT SQLSVR Datasrc=Audit04 SCHEMA=dbo ;
%let LibRefAudit = dbo;
%let DqzDateVerTime = ;
%let DataProvider = ;
%let OFR_Batch_ID = 3769;
%let etls_jobName = ;
%let Environment = DATA_DEV;
%let dataset = ;
%let job_rc = 0 ;

proc sql;
CREATE TABLE DataReport AS
SELECT dataset_name,JobName,TargetDatabase,TargetServer,SourceFileName,
SourceFileRowCount,PostLoadTargetRowCount,TargetSchema,
TargetTableName,OFR_Batch_ID,OFRLoadStartDatetime,OFRLoadEndDateTime ,JobReturnCode
from ETLAUDIT.ETL_JOB_AUDIT where dataset_name = "&dataset" and ParentJobName = "&etls_jobName" and DqzDateVerTime = "&DqzDateVerTime" and DataProvider = "&DataProvider" and "&OFR_Batch_ID"
ORDER BY OFRLoadStartDatetime asc;
quit;

proc sql;
/*CREATE TABLE Time AS*/
SELECT
min(OFRLoadStartDatetime) format=datetime20., max(OFRLoadEndDateTime) format=datetime20. ,ParentJiraTicketUrl into : minTime , : maxTime, : ParentJiraTicketUrl
from ETLAUDIT.ETL_JOB_AUDIT where dataset_name = "&dataset" and ParentJobName = "&etls_jobName" and DqzDateVerTime = "&DqzDateVerTime" and DataProvider = "&DataProvider" and "&OFR_Batch_ID"
ORDER BY OFRLoadStartDatetime desc;
quit;

%put &minTime;
%put &maxTime;

/*ODS tagsets.excelxp options(sheet_name='DataSetCounts' autofilter='all' frozen_headers='yes' orientation='landscape' absolute_column_width ='3,7,30,30,35,10,13,8,23,30,3,50,20,20')*/
/* FILE="/data/ETL_work/&Environment/Reports/&dataset..xls" style=HTMLblue;*/
/**/
/*ODS tagsets.excelxp close;*/

Filename output email to="jonathan.marshall@"
content_type="text/html"
subject="&dataset. Report" Attach="/data/ETL_work/DATA_DEV/Reports/&dataset..xls";;
ods html3 file=output style=HTMLblue;

/*ods msoffice2k file=output style=HTMLblue*/
/*metatext='name="viewport" content="width="device-width"'*/
/*options(pagebreak="no");*/

/*ods tagsets.Tableeditor file=output options(format_email='yes'*/
/*pagebreak="no" rowheader_bgcolor=”green”*/
/* background_color="white"*/
/* rowheader_fgcolor="white"*/
/* header_bgcolor="#b0b0b0"*/
/* header_fgcolor="green"*/
/* data_bgcolor="#b0b0b0"*/
/* col_color_even=”#e0e0e0”*/
/* col_color_odd=”white”*/
/* gridlines=”cols”);*/
/**/
/*style=HTMLblue;*/

title1 "&dataset Report";
proc odstext;
list;
item "Profile Summary for &dataset";
item "Date of &sysdate";
item "TimeStamp: &DqzDateVerTime";
item "StartTime: &minTime";
item "EndTime: &maxTime";
item "JiraTicket: &ParentJiraTicketUrl";
end;
run;
/*proc print data=sashelp.orsales;*/
/*var Year Product_Line Product_Group Quantity Profit;*/
/*run;*/

proc Report nowd data=DataReport
style(header)=[background=#f0f0f0 foreground=black vjust=bottom]
style(report)=[cellspacing=1 borderwidth=1 ];
col dataset_name JobName TargetDatabase TargetServer SourceFileName SourceFileRowCount PostLoadTargetRowCount TargetSchema TargetTableName OFR_Batch_ID OFRLoadStartDatetime OFRLoadEndDateTime JobReturnCode;
define dataset_name--JobReturnCode / display;
define dataset_name / display 'DataSetName';
define OFRLoadStartDatetime / display 'StartTime';
define OFRLoadEndDateTime / display 'EndTime';
compute dataset_name;
bg + 1;
if mod(bg, 2) = 1 then
call define(_row_, "style", "style={background=#C2C9CC}");
else
call define(_row_, "style", "style={background=#C3DCE6}");
endcomp;
run;

/*ODS tagsets.excelxp close;*/

ods _all_ close;

Re: Conditional E-mail Report

Cynthia_sas — Sat, 31 Oct 2015 17:30:22 GMT

Hi:
It looks to me like you are confusing how to use DATA step IF conditions and SAS Macro %IF conditions. To process WHOLE steps and statements conditionally, you would not put your code into a DATA step program, you would typically put your code into a Macro program and test your condition using %IF and then generate your code (DATA step, PROC step, Global statement like FILENAME) inside the Macro program.

cynthia

Re: Conditional E-mail Report

jdmarshg — Sat, 31 Oct 2015 22:18:07 GMT

Hello Cynthia,

I was able to resolve this issue last night.

Yes your definitely right. I created a macro outside of the data set and then was able to call from within the data step.

I'm still learning Base SAS and Macro Language.

Regards,

jonathan

topic Conditional E-mail Report in ODS and Base Reporting

Conditional E-mail Report

Re: Conditional E-mail Report

Re: Conditional E-mail Report