Send results of PROC SQL in an e-mail automatically

DrZenith · Posted 12-06-2011 04:22 PM

Hey guys...

On googling, I found the following template to send the output in an email automatically,

%let subject = %str(Email configuration test);

%let fromEmail = '<Email Address>';

%let toEmail = '<Email Address>';

%let path = C:\documents and settings\&sysuserid.\my documents\test.htm;

FILENAME OUTPUT EMAIL

SUBJECT = "&subject."

FROM = &fromEmail.

TO = &toEmail.

CT ='text/html';

data _NULL_;

file output;

put "Working";

run;

I have a couple of SQL queries (Select queries) which I have written in PROC SQL in a SAS program, I want the output in HTML format to be sent in an email automatically. How would I change the above template or any other simpler method??

Peter_C · Posted 12-06-2011 04:56 PM

have a look at ATTACH E-mail option of the FILENAME statement in the doc at http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002058232.htm

FriedEgg · Posted 12-06-2011 08:34 PM

filename eml email to='some@work.com';

proc printto print=eml;

proc sql;

select * from sashelp.class;

quit;

proc printto;

FriedEgg · Posted 12-06-2011 08:38 PM

Missed that you wanted it in html format...

filename eml email to='someone@work.com' ct='text/html';

ods listing close;

ods html body=eml;

ods select sql_results;

ods noproctitle;

proc sql;

select * from sashelp.class;

quit;

GreggB · Posted 12-07-2011 09:06 AM

I tried this but it looks like it delivered html code rather than output. What do you think?

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<title>SAS Output</title>

<!--

NN · Posted 12-07-2011 10:03 AM

This is something that i tried and got a proper result

ods _all_ close;

filename message email

to="myid@mymail.com"

from = "myid@mymail.com"

subject = "test"

ct="text/html";

ods html body = message style = minimal rs=none;

proc sql;

select * from sashelp.class;

run;

ods html close;

ods listing;

FriedEgg · Posted 12-07-2011 10:46 AM

GreggB,

This is normally the result of not setting the content type on the filename statement.

filename eml email to='someone@work.com' ct='text/html';

DrZenith · Posted 12-08-2011 05:19 AM

Thanks FriedEgg (Good name BTW:smileygrin:), it helped me a great deal, but its sending me the output (HTML format) embedded in the mail, not as an attachment. Any help on that would be great!

polingjw · Posted 12-08-2011 09:03 AM

If you are using the default MAPI email interface, you need to switch to using SMTP email in order to include HTML formatted email in the body of the message.

FriedEgg · Posted 12-08-2011 11:47 AM

Oh, you want to send as attachment....

filename tmp temp;

%let afile=%sysfunc(pathname(tmp));

filename eml email

to='mkastin@i-behavior.com'

subject='attach something'

attach=("&afile" ct='text/html' name='procsql_results' ext='html');

ods html body=tmp;

proc sql;

select * from sashelp.class;

quit;

ods html close;

data _null_;

file eml;

put "It's done";

run;

murphykobe · Posted 02-07-2018 05:11 PM

Thank you very much sir,

Is there possible to add a logic check only sent email if the sql return is not null?