topic Re: How do I Program Conditional Email Distribution? in SAS Programming

How do I Program Conditional Email Distribution?

jonwane — Thu, 09 Feb 2017 19:29:52 GMT

Hi. I'm new to SAS 9.4 and this forum.

I've created a table that changes daily.

Included in my table are four columns of coworker email addresses that I need to email the report to.

I am able to email the report to myself using the TO= command, but I am not able to find any documentation that helps me understand how to use the column variables for emailing.

Does this make sense?

Again, the email addresses on the report change every day, so static email addresses don't work.

Any programming suggestions (or links) would be appreciated.

FILENAME mail EMAIL

FROM="work_email@work.com"

TO=("?????????????????????????????")

REPLYTO="work_email@work.com"

SUBJECT="Notification Table to Coworkers"

CONTENT_TYPE="text/html";

ODS LISTING close;

ODS HTML BODY=mail options(pagebreak="no") STYLE=EGDefault;

Re: How do I Program Conditional Email Distribution?

Shmuel — Thu, 09 Feb 2017 20:04:52 GMT

You should create a macro variable containing the relevant emails and use it in the TO statement.

Post a sample of your data that contains the emails, the rules to decide who of them are relevant,

and you'll get in replay how to create the macro variable and how to use it in the email

Re: How do I Program Conditional Email Distribution?

data_null__ — Thu, 09 Feb 2017 20:17:52 GMT

@jonwane wrote:

Hi. I'm new to SAS 9.4 and this forum.

I've created a table that changes daily.

Included in my table are four columns of coworker email addresses that I need to email the report to.

I am able to email the report to myself using the TO= command, but I am not able to find any documentation that helps me understand how to use the column variables for emailing.

Does this make sense?

Again, the email addresses on the report change every day, so static email addresses don't work.

Any programming suggestions (or links) would be appreciated.

FILENAME mail EMAIL

FROM="work_email@work.com"

TO=("?????????????????????????????")

REPLYTO="work_email@work.com"

SUBJECT="Notification Table to Coworkers"

CONTENT_TYPE="text/html";

ODS LISTING close;

ODS HTML BODY=mail options(pagebreak="no") STYLE=EGDefault;

Go here

http://support.sas.com/documentation/cdl/en/lestmtsref/69738/HTML/default/viewer.htm#n0ig2krarrz6vtn1aw9zzvtez4qo.htm

and read it all paying close attention to the section.

PUT Statement Email Directives

Re: How do I Program Conditional Email Distribution?

jonwane — Mon, 13 Feb 2017 19:59:51 GMT

Hi Shmuel,

Thank you. Please excuse my delayed response.

So, in my report, I have 4 columns of emails (some blanks and some duplicate email addresses)

In my Process Flow, I created filtering for the four columns of email addresses, I appended them into one table, and then in a new table titled "Combined Emails" I combined all the email addresses into one column, removed duplicates, and added quotations around each email address.

So now, I have a table that will repopulate all the emails I need when I run the branch.

My questions is, how do I add that column to the TO= statement within my program so it will mail out to the people on the "Combined Emails" table?

If more info is needed, I will gladly supply it.

Thank you again.

Re: How do I Program Conditional Email Distribution?

Shmuel — Mon, 13 Feb 2017 20:42:05 GMT

If I'm right you create a table with a column containing the relevane emails.

Next code will create such table with 3 emails:

data emails;

infile datalines truncover;

input adrs $;

datalines;

person1@site1.com

person2@site2.com

person3@site3.com

;run;

/* creating macro variable with all relevant emails */

data _NULL_;

set emails end=eof;

length addresses $100; /* adapt length to maximum expected */

retain addresses;

addresses = catx('" "', addresses, adrs)'

if eof then do;

addresses = '"' || addresses ||'"';

call symput('Emails', addresses);

end;

run;

Then, creating the email:

TO = ( &Emails );

Re: How do I Program Conditional Email Distribution?

jonwane — Tue, 14 Feb 2017 12:16:53 GMT

Hi Shumel,

Thank you for taking your valuable time to help me.

Your solution appears to refer to static addresses:

datalines;

person1@site1.com

person2@site2.com

person3@site3.com

Unfortunately, my email addresses are not static, meaning they change every time I run the report (daily). In my report, there are a total of 10,000+ possible email addresses and the report I need to distribute might have as few as 3 or as many as several hundred, depending on what accounts are "flagged" on my report for that day.

(1) I run an aggregated report (report1) I created in a separate process flow (processflow1)

(2) In my new process flow (processflow2), I created a query that takes some of the data from report1 and creates report2.

(3) Now were done with processflow1 and report1

(4) In processflow2 I wrote a program that highlights daily changes within the report using ODS, HTML, PROC REPORT, and EMAIL commands.

(5) Within report2 are four columns of email addresses that change daily, depending on what accounts are "flagged"

(6) I created some filtering and a separate table (combinedemails) within processflow2 that appends all four columns of emails into one column, removes duplicates, removes empty cells, and places quotation marks around each email address.

(7) The report looks good, when I email it to myself.

(8) My chanllenge is getting the combinedemails table into the TO= so it will email out to them, not just me.

Does this make sense?

Thank you again for any advice you might offer.

Re: How do I Program Conditional Email Distribution?

Shmuel — Tue, 14 Feb 2017 14:23:10 GMT

@jonwane, you wrote:

(6) I created some filtering and a separate table (combinedemails) within processflow2

that appends all four columns of emails into one column, removes duplicates, removes empty cells,

and places quotation marks around each email address.

I did not use static list of emails. In my first step:

data emails;
  infile datalines  truncover;
  input adrs $;
datalines;
person1@site1.com
person2@site2.com
person3@site3.com
;run;

I have just created a test dataset containing some emails in one column.

Any time you run your report, there will be different (number of) emails.

The next step creates the macro variable to hold list of emails to be used in TO statement

creating the report. You need addapt input table name and variable names in that step.

data _NULL_;
  set  emails end=eof;
        length addresses $100;    /* adapt length to maximum expected */
        retain  addresses;
        addresses = catx('"  "', addresses, adrs)'
        if eof then do;
            addresses = '"' || addresses ||'"';
            call symput('Emails', addresses);
        end;
run;
 
Then, creating the email:
     TO = ( &Emails );

You can check the list by entering code :

%put EMAILS = &emails;

immediately after 2nd step, before the reporting step.

In case it doesn't work as expected, post your log - the relevant part.

Re: How do I Program Conditional Email Distribution?

data_null__ — Tue, 14 Feb 2017 17:07:41 GMT

@jonwane wrote:

Hi Shumel,

Thank you for taking your valuable time to help me.

Your solution appears to refer to static addresses:

datalines;

person1@site1.com

person2@site2.com

person3@site3.com

Unfortunately, my email addresses are not static, meaning they change every time I run the report (daily). In my report, there are a total of 10,000+ possible email addresses and the report I need to distribute might have as few as 3 or as many as several hundred, depending on what accounts are "flagged" on my report for that day.

(1) I run an aggregated report (report1) I created in a separate process flow (processflow1)

(2) In my new process flow (processflow2), I created a query that takes some of the data from report1 and creates report2.

(3) Now were done with processflow1 and report1

(4) In processflow2 I wrote a program that highlights daily changes within the report using ODS, HTML, PROC REPORT, and EMAIL commands.

(5) Within report2 are four columns of email addresses that change daily, depending on what accounts are "flagged"

(6) I created some filtering and a separate table (combinedemails) within processflow2 that appends all four columns of emails into one column, removes duplicates, removes empty cells, and places quotation marks around each email address.

(7) The report looks good, when I email it to myself.

(8) My chanllenge is getting the combinedemails table into the TO= so it will email out to them, not just me.

Does this make sense?

Thank you again for any advice you might offer.

Go here

http://support.sas.com/documentation/cdl/en/lestmtsref/69738/HTML/default/viewer.htm#n0ig2krarrz6vtn...

and read it all paying close attention to the section.

PUT Statement Email Directives