Happy Friday, Everyone,
I have a scenario wherein I need to send an email every day but only attach a file if there are results. Basically the code monitors a scenario wherein an item that was placed into the 'Ready for report' status for batch upload and then pulled back to an 'In progress' status (where it will not be picked up by the evening batch run). What I'm trying to do is to always send an email but only include an attached Excel file if there are results. This is required to satisfy internal controls/audits showing the report is running but only sending a file when there are items requiring attention.
proc sql;
create table ITEM_STEP_BACK as
select t1.alert_id
,t2.status_name as CASE_STATUS
,t3.form_identifier
,t3.e_status
,t7.cd12 as CASE_ITEM_CURRENT_DUE_DATE
,intck('days',today(),(datepart(t7.cd12))) as DAYS_TO_DUE_DATE
,scan(scan(t5.note,3,']'),2,'[') length=45 as STEPPED_FROM
,scan(scan(t5.note,4,']'),2,'[') length=45 as STEPPED_TO
,datepart(t5.create_date) format=date9. as NOTE_DATE
,t6.full_name as STEPPED_BACK_BY
from rcmr.alerts t1
,rcmr.acm_md_alert_statuses t2
,rcmr.acm_forms t3
,rcmr.acm_alert_audits t4
,rcmr.acm_audits t5
,rcmr.acm_users t6
,rcmr.acm_alert_custom_attributes t7
where t1.status_internal_id = t2.status_internal_id
and t1.alert_internal_id = t3.alert_internal_id
and t1.alert_internal_id = t4.alert_internal_id
and t4.audit_internal_id = t5.audit_internal_id
and t5.user_internal_id = t6.user_internal_id
and t1.alert_custom_attributes_id = t7.alert_custom_attributes_id
and t1.alert_type_internal_id in (324, 325, 327, 328)
and (t5.create_date >= intnx('days',today(),-45,'b')
and t5.create_date < intnx('days',today(),0,'b'))
and (t5.note like 'Type: [ITEM], Identifier: [%] - Step changed from [Ready for report] to [In process]'
or t5.note like 'Type: [ITEM], Identifier: [%] - Step changed from [Ready for report] to [Reported externally]'
or t5.note like 'Type: [ITEM], Identifier: [%] - Step changed from [In process] to [Reported externally]'
or t5.note like 'Type: [ITEM], Identifier: [%] - Step changed from [%] to [Closed as non issue]')
and t3.e_status not in ('Ready for report', 'Reported - pending', 'Reported - success')
/*
and intck('days',today(),(datepart(t7.cd12))) < 7
*/
order by t3.create_date;
quit;
title "Item Stepped Back"
;
proc print data=item_step_back;
run;
*******************************************;
* EXPORT ITEM STEP BACK RESULTS TO EXCEL. ;
*******************************************;
data _null_;
call symput('repdate',put(today(),date9. -L));
call symput('repdate2',put(intnx('days',today(),-1),date9. -L));
call symput('repdate3',put(today(),weekdate29. -L));
call symput('repdate4',put(intnx('days',today(),-1),weekdate29. -L));
run;
%put repdate : &repdate; * TODAY'S DATE in DATE9. ALIGNED LEFT ;
%put repdate2 : &repdate2; * YESTERDAY'S DATE in DATE9. ALIGNED LEFT ;
%put repdate3 : &repdate3; * TODAY'S DATE IN WEEKDATE29. ALIGNED LEFT ;
%put repdate4 : &repdate4; * YESTERDAY'S DATE in WEEKDATE29. ALIGNED LEFT ;
proc export
data=ITEM_STEP_BACK
dbms=xlsx
outfile="FILEPATH_&repdate..xlsx"
replace;
sheet="ITEM_STEP_BACK_&repdate";
run;
*********************************;
*EMAIL RESULTS - ITEM STEP BACK. ;
*********************************;
filename mail email
FROM=( "me@xxxx.com" )
TO=( "manager@xxxx.com" )
CC=( "me@xxxx.com" )
TYPE='TEXT/HTML'
SUBJECT="Item Step Back Reporting for &repdate3"
LRECL=32767
ATTACH=("FILEPATH_&repdate..xlsx"
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
data _null_;
file mail;
put "<html><head>";
put "Good Morning,<br>";
put "<br>";
put "See attached files for Items that were stepped back from 'Ready for report' and into another, possible problematic, status.<br>";
put "<br>";
put "THANKS!<br>";
put "<br>";
put "Matt D<br>";
put "</body></html>";
run;
title;
Again, this code works just fine, but I'd like to get to where it is only exporting a file should results exist and attaching it to the email. The email does need to go out every day, but only include the Excel attachment when there are results.
THANKS!
... View more
