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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.