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;
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.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.