Using macros in a do loop to send a Notification EMAIL

[kmardinian]

Hi, I am have a program that is supposed to send out different email messages based on whether a certain condition is met. I have created strings of all my variables to feed them through a do loop to send out each individual email. I have successfully created the string of variables, but can't seem to figure out how to call them in my email code. I am not sure whether I can call macro variables using a put statement in another macro. I have my code below, any help is much appreciated!

proc sql noprint;
select SUBJECT, EMAIL, BODY1, count(SUBJECT)
into
:SUBJECT separated by '|',
:EMAIL separated by '|',
:BODY1 separated by '|',

:n
from all;
quit;
%put subject=&subject;

options emailsys=XX emailhost='XXX.XXXX.com' emailauthprotocol=none emailid="XXXXXXXXXXXXXX.com" ;

options mprint;
%macro email;
%do i=1 %to &n;
%let SUBJECT=%scan(&SUBJECT,&i,'|');
%let EMAIL=%scan(&EMAIL,&i,'|');
%let BODY1=%scan(&BODY1,&i,'|');

%macro sendemail;
*write an email;
filename outbox email;
data _null_;
file outbox
to=("XXXXXX.com")

subject="&EMAIL";

put "&body1";
put ' ';
put 'SUBJECT:&SUBJECT';

put ' ';
put 'Thanks,';

run;
filename outbox clear;

%mend;
%sendemail;
%end;
%mend;
%email;

[Reeza]

My suggestion would be to redesign your process.
Create a macro to send the email with the parameters needed, driven from the data in your all data set. Call the macro using CALL EXECUTE instead of looping, which makes conditional logic much easier.

[kmardinian]

Hi Reeza, I'm not exactly sure what you mean, you could write out a sample of what you mean? Thank you!

[Reeza]

basically this idea.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

It seems you already have your data in a dataset since you're using that to create macro variables. 

Instead, change your macro program to send an email provided the body, subject and email parameters. 

%macro emailClients(email=, subject=, body=);

....code ...

%mend;

Now, like in my example linked, call that macro from your data set.

data test;
set all;

str = catt('%emailClient(email=', email, ', subject=', subject, ',body=', body, ');');

if run='Y' then call execute(str);

run;

I usually keep it in a data set so I can check that the str variable is created correctly and once that's verified, I change it to a data _null_ step so that it's cleaner. 

[kmardinian]

Hi Reeza, I see what you mean. The only reason I think this might not work for my specific program is because the emails are generated based on certain conditions. So I won't know when one email is generated over another. Which is why I thought it would be easier to just create a string of each variable and just run the do loop through them (because the email body/subject/ etc might be different) I hope that makes sense, thank you again for all the help on this!

[Astounding]

While I agree with @Reeza that you are using more macro language than I would, you could keep your current program.  However, there is a laundry list of issues to consider.

1. If you were to hard-code your logic (two sets of FILENAME statements and DATA _null_ steps) using hard-coded values with zero macro language, would the program send out two emails as planned or would it somehow (perhaps the FILENAME statements) fail?
2. You have a macro variable reference ('SUBJECT:&SUBJECT') within single quotes.  Those need to be double quotes for the macro variable reference to resolve.
3. The %SCAN functions are using the wrong third parameter:  '|'  Instead, it should be:  |    Macro language does not need quotes to indicate that it is processing a character string.  Using the quotes means that both single quotes and pipes are delimiters.  So if any of your SUBJECT strings contain single quotes, the logic will be wrong.
4. Defining a macro within the definition of another macro is bad practice.  You can call one macro from another, but keep the definitions separate.
5. You replace your macro variables (&SUBJECT, &EMAIL, and &BODY1) with new values inside your %DO loop.  So after the first iteration, they are all truncated and contain only one varlue.

Beyond that, you didn't tell us what went wrong.  Why do you say it isn't working?  What did the log show?  

[kmardinian]

So when the macros are not used, I can successfully send an email, but it will only send an email for the last subject in the dataset, which is why I was trying to create a do-loop to get separate emails sent for each subject.

I tried removing the quotations for '|' but it gave me an error, I have checked that the strings for all those variables were successfully created, so I think this part of the code is working ok.

What ends up happening though, is the multiple emails are sent correctly, but they only include the text I have written out in the email, none of the actual macro values are recognized. Only the very first email is correct and has all the information, so it is something with my do loop

EMAIL EXAMPLE:

subject= (no subject shows up)

''

''

SUBJECT:

Thanks,

[kmardinian]

It seems like my do loop is not running past n=1 for some reason

[ScottBass]

Perhaps this will give you some ideas?

https://github.com/scottbass/SAS/blob/master/Macro/sendmail.sas

[Astounding]

That's related to my original point #5. Before the loop begins, &SUBJECT (for example) contains a set of values. As the loop begins (&n=1), &SUBJECT gets replaced using the %SCAN function so it now contains only the first item on the original list. All 5 points are important. This shows why point #5 makes a difference.

[kmardinian]

I see, what would be the alternative though? Because I thought using the scan function was necessary to go through the string of values that I created for each variable.

[Kurt_Bremser]

First, use the "little running man" button for posting code, so we don't get smileys and scrambled code formatting.

Second, NEVER define macros inside each other. This serves NO purpose at all, as all macros are defined in the global symbol table, and only clutters up the definition of the outside macro, making it less readable and maintainable.

Third, there's no need to intermediately store lists in macro variables from a dataset when you can directly execute your code from that dataset:

%macro sendemail(email=,body1=,subject=);
*write an email;
filename outbox email;
data _null_;
file outbox
  to=("XXXXXX.com")
  subject="&EMAIL"
;
put "&body1";
put ' ';
put "SUBJECT:&SUBJECT";
put ' ';
put 'Thanks,';
run;
filename outbox clear;
%mend;

data _null_;
set all;
call execute(cats('%nrstr(%sendmail(email=',email,',body1=',body1,',subject=',subject,'))'));
run;

Here, I only used the macro definition to make the call execute shorter. In RL, I usually put the code right into the data step like this:

data _null_;
set all;
call execute('
filename outbox email;
data _null_;
file outbox
  to=("XXXXXX.com")
  subject="' !! EMAIL !! '"
;
put "' !! strip(body1) !!'";
put " ";
put "SUBJECT:' !! strip(SUBJECT) !! '";
put " ";
put "Thanks,";
run;
filename outbox clear;
');
run;

Note that the subject would not have worked in your original code because of the single quotes.

[ScottBass]

Hi, I am have a program that is supposed to send out different email messages based on whether a certain condition is met.

You don't really go into enough detail on this.  What condition?  And what's supposed to happen when that condition is met?  Does the Subject change?  The To: list change?  Is the email body the same, just the subject is different?  You know you can put more than one address in the To: list, right?  You don't have to send separate emails one at a time.

In addition to my previous sendmail link, perhaps these will help.  Read the macro header for use cases:

https://github.com/scottbass/SAS/blob/master/Macro/loop.sas

https://github.com/scottbass/SAS/blob/master/Macro/loop_control.sas

For example, with loop_control, you could create a metadata dataset with your To, Subject, Body, etc, plus some filtering condition.  Then call your child macro, filtering your control table with a where clause.

[Astounding · Accepted solution]

The alternative?

Why replace &SUBJECT?

Create a new macro variable:

%let subject2 = %scan(&subject, &n, |) ;

Use the new macro variable within %email.

Same idea for the remaining two macro variables.

[kmardinian]

Thank you, that worked perfectly and exactly how I needed!

Thank you to everyone for their help on this. I need to keep working on the proper way of coding macros...still learning.