topic Send results of PROC SQL in an e-mail automatically in SAS Procedures

Send results of PROC SQL in an e-mail automatically

DrZenith — Tue, 06 Dec 2011 21:22:22 GMT

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

Send results of PROC SQL in an e-mail automatically

Peter_C — Tue, 06 Dec 2011 21:56:33 GMT

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

Send results of PROC SQL in an e-mail automatically

FriedEgg — Wed, 07 Dec 2011 01:34:19 GMT

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

proc printto print=eml;

proc sql;

select * from sashelp.class;

quit;

proc printto;

Send results of PROC SQL in an e-mail automatically

FriedEgg — Wed, 07 Dec 2011 01:38:46 GMT

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;

Send results of PROC SQL in an e-mail automatically

GreggB — Wed, 07 Dec 2011 14:06:45 GMT

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>

<!--

Re: Send results of PROC SQL in an e-mail automatically

NN — Wed, 07 Dec 2011 15:03:16 GMT

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;

Send results of PROC SQL in an e-mail automatically

FriedEgg — Wed, 07 Dec 2011 15:46:38 GMT

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

Send results of PROC SQL in an e-mail automatically

DrZenith — Thu, 08 Dec 2011 10:19:30 GMT

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!

Send results of PROC SQL in an e-mail automatically

polingjw — Thu, 08 Dec 2011 14:03:59 GMT

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.

Send results of PROC SQL in an e-mail automatically

FriedEgg — Thu, 08 Dec 2011 16:47:44 GMT

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;

Re: Send results of PROC SQL in an e-mail automatically

murphykobe — Wed, 07 Feb 2018 22:11:43 GMT

Thank you very much sir,

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