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;
... View more
