Using macros in a do loop to send a Notification EMAIL

[ErikLund_Jensen]

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;

 

[Astounding]

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.