Automatic Emails from SAS dataset

[domigeek]

Hi, I am trying to create emails based on a distribution list that I have. I cannot get it to work! I have a work.distributionlist dataset with three columns: one with email addresses one with the report that the end user receives and one autonumber column. I would like to send an email that says " "emailaddress1" you receive "report1" " with the email address and report autopopulated by the dataset file. I need it to then loop and send the same email to emailaddress2 and emailaddress3, etc... I tried creating a macro, but I'm not very good at this and can't figure out how to do it. Help would be appreciated! Here's my code so far (I'm in SAS 9.4):

*Create macro for looping; 
%macro createdistro_lists; 
%do autonumber = 1 %to 5; 
%global email&autonumber.var; 
%global report&autonumber.var; 
*store distribution lists to a variable; 
proc sql noprint; select trimn(email_address) into: email&autonumber.var from work.distribution_list; 
run; 
proc sql noprint; select trimn(report_received) into: report&autonumber.var from work.distribution_list; 
run; 
%put email&autonumber. = &&main&autonumber._var; 
%put report&autonumber. = &&report_&autonumber._var; 
%end; 
%mend; %create_distro_lists;
*Send emails; 
filename sendmail email; data null; 
file sendmail to=("&email&autonumber.") 
PUT "Hello"; put ' '; 
put " We have noted that you receive the following reports. 
Please confirm that this report is still in use. 
Unused reports will be retired."; 
put ' '; PUT "&report&autonumber."; 
PUT " "; PUT "Regards,";
run;

[ChrisHemedinger]

The e-mail list should contain quoted e-mail addresses, separated by spaces.  Use the CAT function (or similar) to build the components of the list in SQL.  Here's what I use:

filename dl "/u/myid/distlist.csv";
data distlist;
  infile dl dsd;
  length email $ 50;
  input email;
run;

proc sql noprint;
select cat('"',trim(email),'"') into :toList separated by ' ' from distlist;
quit;

FILENAME OUTPUT EMAIL
 SUBJECT = "Super message"
 FROM = "sender <myemail@company.com>"
 TO = (&toList)
   ;

[domigeek]

Thanks for your response. 

The issue I have with this is that I want to take the emails from a table that's alreay in SAS and I want to send 1 email per email_address with the name of their report in the message body. There are 200 some-odd email addresses so I should have that same number of emails with different report names in the email body.

[ccaulkins91]

I was doing some research this morning - wanted to make another thread available - from 2010 (!)
if I can just find it to compare that code with what domi seems to have come up with.

[domigeek · Accepted solution]

So I figured it out and wanted to post for others:

*Create last record variable;
proc sql; 
select trim( put( count(*), 3.) ) 
into : number_of_records
from work.distribution_list;
run;

*Create email variables;
proc sql; 
select email_address 
into :emails_1-:emails_&number_of_records.
from work.distribution_list;
run;
 
*Create report variables;
proc sql; 
select reports_received 
into :reports_received_1-:reports_received_&number_of_records.
from work.distribution_list;
run;

/*when you run this macro all your emails will get generated. change the '4' below to &number_of_records. when you are ready to run it all!*/
%macro sendEmails;
%do counter = 1 %to 4;  

filename sendmail email;
data _null_;
	
file sendmail
to=("&&emails_&counter.");
PUT '!em_sub! Reports Audit'; 
PUT "Hello"; PUT ' '; 
PUT " We have noted that you receive the following reports. 
Please confirm that this report is still in use. 
Unused reports will be retired."; 
PUT ' '; PUT "&&reports_received_&counter."; 
PUT " "; PUT "Regards,";
	%runquit; 
%end;
%mend sendEmails;

[ccaulkins91]

And I also wanted to throw in a "shameless" plug for WRS - web report studio - and the scheduling distribution of report capabilities that have been there since 3.1. there the email lists are stored in the wrsdist library in Management Console and -

maybe I could write it up in the WRS community to be fair.