topic Re: Conditional Export to Excel in SAS Programming

Conditional Export to Excel

hnb_matt_d — Fri, 30 Apr 2021 20:31:52 GMT

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!

Re: Conditional Export to Excel

PaigeMiller — Fri, 30 Apr 2021 20:37:34 GMT

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

Re: Conditional Export to Excel

hnb_matt_d — Tue, 04 May 2021 15:48:17 GMT

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

Re: Conditional Export to Excel

Tom — Tue, 04 May 2021 16:10:28 GMT

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

Re: Conditional Export to Excel

PaigeMiller — Tue, 04 May 2021 16:29:03 GMT

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.

Re: Conditional Export to Excel

hnb_matt_d — Thu, 20 May 2021 16:52:16 GMT

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.

Re: Conditional Export to Excel

hnb_matt_d — Thu, 20 May 2021 17:20:21 GMT

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.

Re: Conditional Export to Excel

Tom — Thu, 20 May 2021 17:31:19 GMT

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

...

Re: Conditional Export to Excel

hnb_matt_d — Thu, 20 May 2021 17:55:11 GMT

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

Re: Conditional Export to Excel

hnb_matt_d — Thu, 20 May 2021 17:58:45 GMT

*****************;
*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;

Re: Conditional Export to Excel

Tom — Thu, 20 May 2021 18:07:37 GMT

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.

Re: Conditional Export to Excel

Kurt_Bremser — Thu, 20 May 2021 18:08:39 GMT

Your code misses a %END.

Re: Conditional Export to Excel

hnb_matt_d — Thu, 20 May 2021 18:09:26 GMT

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

Re: Conditional Export to Excel

Kurt_Bremser — Thu, 20 May 2021 18:12:27 GMT

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.

Re: Conditional Export to Excel

hnb_matt_d — Thu, 20 May 2021 18:13:02 GMT

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.

Re: Conditional Export to Excel

hnb_matt_d — Thu, 20 May 2021 18:15:02 GMT

Above errors are from running this:

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

Re: Conditional Export to Excel

hnb_matt_d — Thu, 20 May 2021 18:21:14 GMT

Closer, but still a NO. It's telling me the %IF and %END statements aren't valid in open code. It's also STILL sending an empty Excel file.

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

Re: Conditional Export to Excel

Kurt_Bremser — Thu, 20 May 2021 18:30:24 GMT

This means that you have an older SAS version where the "open code" %IF was not possible.

You need to wrap your code into a macro definition and call the macro:

filename mail email 
FROM=( ".com" )
TO=( ".com"  )
CC=(  ) 
TYPE='TEXT/HTML'
SUBJECT="SUBJECT &repdate"
LRECL=32767
%macro cond_attach;
%if (&num_recs >0) %then %do;
ATTACH=("FILEPATH_&repdate..xlsx"
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
%end;
%mend cond_attach;
%cond_attach /* no semicolon here! */

Re: Conditional Export to Excel

Tom — Thu, 20 May 2021 18:30:53 GMT

@hnb_matt_d wrote:

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.

You have a number of options to fix this.

Talk to your computer support staff to understand why you are running such an old version of SAS.
Start using a macro.

Assuming your SAS version is not even older you could use %SYSFUNC(IFC()) to generate the conditional code.

filename email
....
%sysfunc(ifc(&num_recs >0,ATTACH=("FILEPATH_&repdate..xlsx" content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"),))
...
;

Conditionally generate a macro variable that contains the text you want to include in the FILENAME statement.

%let attach=;
data _null_;
  set my_data ;
  call symputx('attach'
     ,'ATTACH=("FILEPATH_&repdate..xlsx" content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")'
     );
  stop;
run;

...
filename email
....
&attach 
...
;

I leave it as an exercise for you to figure out which you want to use and how to update your code to use. Make sure that the code you create is valid SAS code and it is still valid SAS code once the conditional part is generated.