Re: Conditional Export to Excel

hnb_matt_d · Posted 04-30-2021 04:31 PM

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!

PaigeMiller · Posted 04-30-2021 04:37 PM

Plenty of possibilities — Google is your friend

See https://communities.sas.com/t5/SAS-Procedures/Run-macro-only-if-obs-gt-0/td-p/389030

--
Paige Miller

hnb_matt_d · Posted 05-04-2021 11:48 AM

Thank you for the quick reply, @PaigeMiller . I have Googled this to no end and posting here was something of a last resort.

I've reviewed the link you provided, and I'm certain I'm not seeing the forest for the trees, but I cannot find a solution there. Sincerest apologies. I've waited a couple days as sometimes that permits the fog to clear a bit, but to no avail.

Any additional suggestions? If it matters, I am working on SAS EG v7.15 HF7.

Thanks so much.

Matt D

PaigeMiller · Posted 05-04-2021 12:29 PM

In that link, it shows how to determine how many observations are in a data set, and then take action if there are 1 or more observations, and do nothing if there are 0 observations in a data set.

--
Paige Miller

Tom · Posted 05-04-2021 12:10 PM

Let's highlight some of the trees for you.

The place where you are specifying that you want to attach a file is here:

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"
   )
;

So first figure whether or not you want to attach the file and set a macro variable to either 1 (true) or 0 (false). Then modify that statement with macro code so that it only includes the ATTACH= part when you want to attach a file.

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
%if &attach %then %do;
  ATTACH=
   ("FILEPATH_&repdate..xlsx"
    content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
   )
%end;
;

hnb_matt_d · Posted 05-20-2021 12:52 PM

Ok. I'm grasping that the determination to attach the file, or not, needs to be done in the statement creating the email.

How do I do that? That's the entire point of this. I don't get how I need to do that.

hnb_matt_d · Posted 05-20-2021 01:20 PM

This piece seems to be better than the select count(t1.alert_id) I was using.

data _null_;
call symput('num_recs',put(numrecs,best.));
set my_data nobs=numrecs;
stop;
run;

It seems to be providing a '0' when there are no results and a '1' when there are one or more results.

Tom · Posted 05-20-2021 01:29 PM

So put the two together:

data _null_;
  call symputX('num_recs',numrecs);
  set my_data nobs=numrecs;
  stop;
run;

....

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
%if (&num_recs > 0) %then %do;
  ATTACH=
   ("FILEPATH_&repdate..xlsx"
    content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
   )
%end;
;

...

hnb_matt_d · Posted 05-20-2021 01:55 PM

It's still attaching an Excel file with no records...

hnb_matt_d · Posted 05-20-2021 01:58 PM

*****************;
*EMAIL RESULTS.  ;
*****************;

filename mail email 
FROM=( "me@XXXX.com" )
TO=( "manager@XXXX.com" )
CC=( "me@XXXX.com" ) 
TYPE='TEXT/HTML'
SUBJECT="EMAIL TITLE for &repdate"
LRECL=32767
%if (&num_recs >0) %then %do;
ATTACH=("FILEPATH_&repdate..xlsx"
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");


data _null_;
file mail;
put "<html><head>";
put "EMAIL BODY TEXT.<br>";
put "<br>";
put "THANKS!<br>";
put "<br>";
put "Matt D<br>";
put "</body></html>";

run;

Tom · Posted 05-20-2021 02:06 PM

You have a %DO without any corresponding %END.

%if (&num_recs >0) %then %do;
  ATTACH=("FILEPATH_&repdate..xlsx"
%end;

You need to have defined NUM_RECS before running the FILENAME statement.

If the code is not inside a macro definition then you must be using a reasonably recent release of SAS for the %IF/%THEN/%DO/%END to work in "open" code.

Kurt_Bremser · Posted 05-20-2021 02:08 PM

Your code misses a %END.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

hnb_matt_d · Posted 05-20-2021 02:09 PM

The %end gives innumerable errors and doesn't run.

Kurt_Bremser · Posted 05-20-2021 02:12 PM

That's because running code with an open %DO often causes your SAS session to become unusable.

Start a new SAS session.

You MUST have a closing %END for each %DO, otherwise the macro processor will (or will not) execute anything you feed it.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

hnb_matt_d · Posted 05-20-2021 02:13 PM

ERROR: The %IF statement is not valid in open code.
ERROR: Error in the FILENAME statement.
ERROR 23-2: Invalid option name end.
ERROR: Insufficient authorization to access /var/spool/mail/srvsasadm.

Ready to join fellow brilliant minds for the SAS Hackathon?

Classroom Training Available!