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;
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;
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)
;
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.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.