topic Error in attachment filename code in SAS Programming

Error in attachment filename code

Q1983 — Thu, 17 Jun 2021 19:04:26 GMT

I am attempting to get a file to email a message and an attachment. The outfile was defined as:

/f1/file09/sascode/reporting/dev/output

The report out information was defined as follows:

%Let ReportName=Capital99_Report_;
%let ReportOut=&ReportName.&today;
%put REPORTNAME=&ReportName;
%put REPORTOUT=&ReportOut;

proc export
data=final
outfile="&outdir/&ReportOut."
label
dbms=xlsx
replace
;
run;

The output writes successfully as follows:
NOTE: The export data set has 104421 observations and 31 variables.
NOTE: "/f1/file09/sascode/reporting/dev/output/Capital99_Report_20210617.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):


filename MI_email
  email
  to=("myemail@bank1.com") 
  subject="Capital99 Report &today." 
  attach = "&outdir/&ReportOut."
;

data _null_; 
FILE MI_email; 
  
PUT "Capital99 Report_&today. is now ENCLOSED in this email.  ";

run;  

However I get this error when I run the filename code above
NOTE: The file MI_EMAIL is:
      E-Mail Access Device

ERROR: Error opening attachment file /f1/file09/sascode/reporting/dev/output/Capital99_Report_20210617.
ERROR: Physical file does not exist, /f1/file09/sascode/reporting/dev/output/Capital99_Report_20210617.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Is it because it is not recognizing the .xlsx extention?? I cannot figure out why this happens because the file clearly writes to the /f1/file09/sascode/reporting/dev/output drive

Re: Error in attachment filename code

ballardw — Thu, 17 Jun 2021 19:03:22 GMT

Does your attachment file name include the file extension? It appears not to. So the code is looking for a file without any extension.

If you want to attach some file named "thisfile.txt" you must use something that resolves to "thisfile.txt", not "thisfile".

Re: Error in attachment filename code

Reeza — Thu, 17 Jun 2021 19:06:30 GMT

Code blocks are for code, put your text in the body of the message. The point of these separators is to make the post more legible and easier to understand, putting everything in one box entirely defeats that purpose.

And yes, you're missing the extension so the file is not attached. What would happen if you have multiple files with the same name and different extensions?

Change attach to :

attach = "&outdir/&ReportOut..xlsx"

Re: Error in attachment filename code

Kurt_Bremser — Thu, 17 Jun 2021 20:04:56 GMT

For clarity, use this in the PROC EXPORT statement:

outfile="&outdir/&ReportOut..xlsx"

and later

attach = "&outdir/&ReportOut..xlsx"

in the FILENAME.

It seems that the EXPORT procedure adds a filename extension on its own if none is supplied.

Re: Error in attachment filename code

Q1983 — Fri, 18 Jun 2021 01:34:50 GMT

This works. I am even able to get the attachment to show in an email. When I try and open the excel I get a corrupt email message. I believe its because the file has 104,000 records although xlsx is supposed to handle this size. I changed the dbms from xlsx to the sequence below:
dbms=EXCELCS
replace
;
SERVER='saspcff';
I get the following error
ERROR: CLI error trying to establish connection: [Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path. Make sure that the path name is
spelled correctly and that you are connected to the server on which the file resides.
ERROR: Error in the LIBNAME statement.
ERROR: Connection Failed. See log for details.
I suspect the server name SERVER='saspcff'; Is there a way to use xlsb without reference to a server name or is there a script in sas that would provide the current server name, assuming that saspcff is incorrect??

Re: Error in attachment filename code

Kurt_Bremser — Fri, 18 Jun 2021 04:16:45 GMT

Download the Excel file directly from the server, and check if it opens outside of an email. Also do a cross-check with LibreOffice.

It may be that the size of the attachment is limited by constraints imposed on the email.

Your ERROR now happens in a LIBNAME, not in PROC EXPORT.