topic Re: E-mail to multiple in SAS Programming

E-mail to multiple

Aidan — Mon, 23 Jan 2017 09:57:10 GMT

Hi there,

I have a work table work.W9QRSD.

The data of which is below.

ALERT EMAIL

76 ME@test.ie
87 ME@test.ie
76 YOU@test.ie
76 YOU@test.ie
20 YOU@test.ie

What I want to do is send the data in this table to each user.

So ME@test.ie will get a mail with the below;

ALERT EMAIL

76 ME@test.ie
87 ME@test.ie

YOU@test.ie will get;

76 YOU@test.ie
76 YOU@test.ie
20 YOU@test.ie

While the below user written code is working for me, it is sending 3 e-mails for YOU@test.ie but I would just want it to be sent once.....any ideas?

Thanks

* send mail from DATA;
%macro mailit(DATA);

%let NOBS=0;
* get emails from DATA;
proc sql noprint;
select EMAIL into :EMAIL1- from &DATA;
select count(*) into :EMAILN from &DATA;
quit;

* cycle through emails;
%do I=1 %to &EMAILN;

filename temp email to="&&&EMAIL&I"
type="text/html"
subject="TEST Daily Alert List";
ods html file=temp;
proc print data=&DATA;
var ALERT;
where EMAIL="&&&EMAIL&I";
run;
ods html close;

%end;

%mend mailit;

%mailit(W9QRSD);

Re: E-mail to multiple

RW9 — Mon, 23 Jan 2017 10:16:44 GMT

Assuming W9QRSD is sorted:

data _null_;
  set w9qrsd;
  by email;
  if first.email then call execute(cats('filename tmp email to="',email,'" type="text/html" subject="Abc"'; data _null_; file temp;));
  call execute(cat('put "',strip(alert),'";'));
  if last.email then call execute(';run;');
run;

This just does basic text, as I don't see anything there which would need html.

If you want however, you could still use you html print.

proc sort data=w9qrsd out=loop nodupkey;
  by email;
run;

data _null_;
  set loop;
  call execute(cats('filename temp "',email,'"; ods html file="temp";'));
  call execute(cats('proc print data=w9qrsd; where email="',email,'"; run;'));
...
run;

Re: E-mail to multiple

Aidan — Mon, 23 Jan 2017 10:19:10 GMT

Thanks for the quick response.....so is the code you provided sufficient to replace what I have entirely or is it to be integrated with what I have?

Re: E-mail to multiple

RW9 — Mon, 23 Jan 2017 10:28:58 GMT

Yes, it replaces it. If you run it, you will see in your log that the datastep generates text at each observation which gets sent to the compiler after the datastep has finished. It will be in your log with + just like an include file. You can create any code you want using this, and the basic principal that a datastep is a loop.

Re: E-mail to multiple

Aidan — Mon, 23 Jan 2017 10:35:19 GMT

I have tired both, while compiling successfully it is not e-mailing to my e-mail address.

proc sort data=work.W9QRSD out=loop nodupkey;
by EMAIL;
run;

data _null_;
set loop;
call execute(cats('filename temp "',EMAIL,'"; ods html file="temp";'));
call execute(cats('proc print data=work.W9QRSD; where email="',EMAIL,'"; run;'));

run;

Re: E-mail to multiple

RW9 — Mon, 23 Jan 2017 10:49:35 GMT

You are missing ods close. Also, I was just re-interpreting the code you provided here;

filename temp email to="&&&EMAIL&I"
type="text/html"
subject="TEST Daily Alert List";
ods html file=temp;
proc print data=&DATA;
var ALERT;
where EMAIL="&&&EMAIL&I";
run;
ods html close;

So to update yours (and use the code window {i} above):

proc sort data=work.w9qrsd out=loop nodupkey;
  by email;
run;

data _null_;
  set loop;
  call execute(cats('filename temp email to="',email,'"; ods html file="temp";'));
  call execute(cats('proc print data=work.w9qrsd; where email="',email,'"; run;'));
  call execute('ods html close;');
run;

Re: E-mail to multiple

Aidan — Mon, 23 Jan 2017 11:07:33 GMT

Sorry, there is no e-mail being generated from the above, I have tried making a few tweaks but nothing seems to work.

It executes without error but no e-mail

Re: E-mail to multiple

RW9 — Mon, 23 Jan 2017 12:31:49 GMT

This works fine for me (although I can't send emails from our setup it just tells me there is no email client associated):

proc sort data=work.w9qrsd out=loop nodupkey;
  by email;
run;

data _null_;
  set loop;
  call execute(cats('filename temp email to="',email,'"; ods html body=temp;'));
  call execute(cats('proc print data=work.w9qrsd; where email="',email,'"; run;'));
  call execute('ods html close;');
run;

Re: E-mail to multiple

Aidan — Mon, 23 Jan 2017 12:34:38 GMT

Thanks for your help but this doesn't send any e-mails for me.

Re: E-mail to multiple

RW9 — Mon, 23 Jan 2017 12:41:21 GMT

What does your log say? You must be doing something wrong, or the code in your original post can't work either. It is not doing anything differently.

Re: E-mail to multiple

Aidan — Mon, 23 Jan 2017 13:40:22 GMT

When I click on output tab in DI, it looks to have what I would want to see in the e-mail.

But no e-mail is generated.

Are you saying your code will send the e-mail or I need to use my own code to do the e-mailing, just getting a bit confused.

proc sort data=work.W9QRSD out=loop nodupkey;
by EMAIL;
run;

data _null_;
set loop;
call execute(cats('filename temp email to="',EMAIL,'"; ods html file="temp";'));
call execute(cats('proc print data=work.W9QRSD; where EMAIL="',EMAIL,'"; run;'));
call execute('ods html close;');
run;

Re: E-mail to multiple

DanielSantos — Mon, 23 Jan 2017 13:54:27 GMT

Hi.

For my original solution, if you want unique EMAILs modify the initial PROC SQL like below:

...
proc sql noprint;
select distinct EMAIL into :EMAIL1- from &DATA;
select count(distinct EMAIL) into :EMAILN from &DATA;
quit;
...

Daniel Santos @ www.cgd.pt

Re: E-mail to multiple

Aidan — Mon, 23 Jan 2017 13:55:06 GMT

Excellent, thank you.......so simple sorry I missed that.

Thank you both for your help