BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aidan
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
DanielSantos
Barite | Level 11

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

View solution in original post

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Barite | Level 11

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
Quartz | Level 8

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

 

Thank you both for your help

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 906 views
  • 1 like
  • 3 in conversation