topic Re: Using macros in a do loop to send a Notification EMAIL in SAS Programming

Using macros in a do loop to send a Notification EMAIL

kmardinian — Wed, 12 Jun 2019 21:54:46 GMT

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;

Re: Using macros in a do loop to send a Notification EMAIL

Reeza — Wed, 12 Jun 2019 22:05:07 GMT

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.

Re: Using macros in a do loop to send a Notification EMAIL

Astounding — Wed, 12 Jun 2019 22:40:49 GMT

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.

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?
You have a macro variable reference ('SUBJECT:&SUBJECT') within single quotes. Those need to be double quotes for the macro variable reference to resolve.
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.
Defining a macro within the definition of another macro is bad practice. You can call one macro from another, but keep the definitions separate.
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?

Re: Using macros in a do loop to send a Notification EMAIL

kmardinian — Wed, 12 Jun 2019 22:42:02 GMT

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

Re: Using macros in a do loop to send a Notification EMAIL

Reeza — Wed, 12 Jun 2019 22:53:25 GMT

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.

Re: Using macros in a do loop to send a Notification EMAIL

kmardinian — Wed, 12 Jun 2019 23:11:36 GMT

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,

Re: Using macros in a do loop to send a Notification EMAIL

kmardinian — Wed, 12 Jun 2019 23:15:22 GMT

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!

Re: Using macros in a do loop to send a Notification EMAIL

kmardinian — Wed, 12 Jun 2019 23:26:21 GMT

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

Re: Using macros in a do loop to send a Notification EMAIL

ScottBass — Thu, 13 Jun 2019 01:42:44 GMT

Perhaps this will give you some ideas?

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

Re: Using macros in a do loop to send a Notification EMAIL

Astounding — Thu, 13 Jun 2019 02:13:19 GMT

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.

Re: Using macros in a do loop to send a Notification EMAIL

kmardinian — Thu, 13 Jun 2019 04:27:21 GMT

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.

Re: Using macros in a do loop to send a Notification EMAIL

Kurt_Bremser — Thu, 13 Jun 2019 06:20:28 GMT

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.

Re: Using macros in a do loop to send a Notification EMAIL

ScottBass — Thu, 13 Jun 2019 06:26:50 GMT

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.

Re: Using macros in a do loop to send a Notification EMAIL

Astounding — Thu, 13 Jun 2019 09:48:24 GMT

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.

Re: Using macros in a do loop to send a Notification EMAIL

ErikLund_Jensen — Thu, 13 Jun 2019 10:14:13 GMT

Hi @kmardinian

Your problem is caused in the do loop, in this construct:

		%let SUBJECT=%scan(&SUBJECT,&i,'|');
		%let EMAIL=%scan(&EMAIL,&i,'|');
		%let BODY1=%scan(&BODY1,&i,'|');

You are reusing the variable name, so first time the loop is running, you take the first string out of SUBJECT and assign it to SUBJECT again, so there is no second value for the next loop run. Same with EMAIL and BODY1.

Use another name in the loop, and it will work. I removed the inner macro, because there is no macro code in it, so it is not needed:

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

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;

%macro email;
	%do i=1 %to &n;
		%let thisSUBJECT=%scan(&SUBJECT,&i,|);
		%let thisEMAIL=%scan(&EMAIL,&i,|);
		%let thisBODY1=%scan(&BODY1,&i,|);

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

			subject="&thisEMAIL";

			put "&thisBODY1";
			put ' ';
			put 'SUBJECT:&thisSUBJECT';

			put ' ';
			put 'Thanks,';

		run;
		filename outbox clear;
%end;
%mend;
%email;

Re: Using macros in a do loop to send a Notification EMAIL

Astounding — Thu, 13 Jun 2019 11:59:50 GMT

Once you've gotten that far, you might as well remove the new macro variables:

%macro email;
	%do i=1 %to &n;
		*write an email;
		filename outbox email;
		data _null_;
			file outbox
			to=("XXXXXX.com")

			subject="%scan(&EMAIL, &i, |)";

			put "%scan(&BODY1, &i, |)";
			put ' ';
			put 'SUBJECT:%scan(&SUBJECT, &i, |)';

			put ' ';
			put 'Thanks,';

		run;
		filename outbox clear;
%end;
%mend;

An important point: the original post represents both a question on an application as well as a learning curve for macro language. So add to the body of knowledge one step at a time.

Re: Using macros in a do loop to send a Notification EMAIL

kmardinian — Thu, 13 Jun 2019 16:14:54 GMT

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.