Conditional Export to Excel

[hnb_matt_d]

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;

[hnb_matt_d]

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

[Kurt_Bremser · Accepted solution]

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! */

[Tom]

---

@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.