E-mail to multiple

[Aidan]

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

[RW9]

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;

[Aidan]

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?

[RW9]

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.

[Aidan]

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;

[RW9]

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;

[Aidan]

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

[RW9]

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;

[Aidan]

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

[RW9]

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.

[Aidan]

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;

[DanielSantos · Accepted solution]

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

[Aidan]

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

Thank you both for your help