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;
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.