CODEWITHSUNDEEP
sqlemailsasexport
user7677227
user7677227

Reputation:

SAS: Proc Export and email error?

I have a code running on a daily basis. At the moment, it's creating an Excel-file (using proc export, see below) which it exports to a file path locally.

proc export data=work.data_set
outfile="FILE LOCATION AND NAME HERE" 
dbms=xlsx
replace;
sheet="Data sheet";
run;

Now I want to change this so that it will still make the excel file but instead of storing it locally, I want to have it send by email. How to do this? I tried to do it like this:

proc export data=work.data_set
  outfile= "FILE LOCATION AND NAME HERE"
  dbms=xlsx
  replace;
  sheet="Data sheet";
run;

        FILENAME demail EMAIL 
                        TO = ("[email protected]")
                        SUBJECT ="Data sheet"
                        FROM = "[email protected]" 
                        ATTACH = (  "FILE LOCATION AND NAME HERE");
        DATA _NULL_;
        FILE demail;
        nu = PUT(TODAY(),DDMMYYD10.);
        PUT 'Hello,';
        PUT;
        PUT 'Bye.'; 
        PUT;
        RUN;

But it gives me this error: "Excel found unreadable content in XXXX.xlsx. Do you want to recover the contents of this workbox? If you trust the source of this workbook, click Yes." Clicking Yes doesn't help.

Thanks!

Upvotes: 2

Views: 549

Answers (2)

momo1644
momo1644

Reputation: 1804

Remove this line from your code: ATTACH = ( "FILE LOCATION AND NAME HERE");

Add these two lines to your code:

%let filepath= FILE LOCATION AND NAME HERE without quotes; 
put "!EM_ATTACH! &filepath"; 

Full Code:

%let filepath= FILE LOCATION AND NAME HERE without quotes; 
proc export data=work.data_set
  outfile= "FILE LOCATION AND NAME HERE"
  dbms=xlsx
  replace;
  sheet="Data sheet";
run;

        FILENAME demail EMAIL 
                        TO = ("[email protected]")
                        SUBJECT ="Data sheet"
                        FROM = "[email protected]" 
                        ;
        DATA _NULL_;
        FILE demail;
        nu = PUT(TODAY(),DDMMYYD10.);
        PUT 'Hello,';
        put "!EM_ATTACH! &filepath"; 
        PUT;
        PUT 'Bye.'; 
        PUT;
        RUN;

Upvotes: 1

samkart
samkart

Reputation: 6644

You have everything correct in your script, except for the filename email.

SAS does not know the content_type of your xlsx file, because as default it considers everything as a binary text file. Hence, messing up with your excel file's integrity. So, you'd have to explicitly mention your file's content_type. So, just a addition to your filename email statement - 

FILENAME demail EMAIL 
TO = ("[email protected]")
SUBJECT ="Data sheet"
FROM = "[email protected]" 
ATTACH = ("%sysfunc(pathname(xllib))/my_excel_file.xlsx" ct="application/xlsx") /*check for the ct, abbr for content_type*/
ct = "text/html";

Then send an email with data _null_;

data _null_;
file demail;
put "Hello";
put "<br><br>"; /*two html style breaks, because it supports html tags for inserting a blank line. Simply put would not insert a line I guess*/
put "Bye!";
run;

Upvotes: 0

Related Questions