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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.