SAS 宏中的迭代 %DO 循环问题，用于自动通过电子邮件发送站点指标

Question

我有一个标题为"SampleReport"的数据集，其中包含特定于站点的报告指标，并希望通过电子邮件将每个站点自己的指标发送到附件的PDF中。PDF 生成并通过电子邮件发送到各自的站点，但是当尝试将 ODS 文本添加到 PDF 时，我在定义其他宏时遇到问题(目前只有"电子邮件"保留为宏变量，但我也想要"站点"、"Site_Contact"和"mcnt_enrolled"(。

下面是测试数据集和我的代码;我是 SAS 的新手，希望得到任何帮助/建议：

site    site_enrolled_total mcnt_enrolled   Site_Contact    Email   Expected_Enrolled   NetworkAvg  Overall_Study_Enrollment    Overall_Enrollment_to_Date
site_a  32  7   Homer Simpson   [e-mail] 40  5   115 1518
site_b  36  8   Jim Schoe   [e-mail]    40  4   115 1518
site_ c 20  2   Hank Hill   [e-mail]   36  7   115 1518
site_d  27  7   Lisa Simpson    [e-mail]   36  5   115 1518

这是我的代码：

%macro getemail(DATA);
ods _all_ close;
%let outdir = %sysfunc(getoption(WORK));
OPTIONS NODATE NONUMBER;
ODS GRAPHICS / RESET=ALL;
** get list of unique emails;
proc sql noprint;
select distinct(email) into :wantemail separated by '~'
from samplereport
order by email;
quit;
%put &=wantemail;
**count number of separators(i.e. '~') and count number of unique emails;
%let cntsep1 = %sysfunc(count(&wantemail,~));
%let cntemail = %eval(&cntsep1+1);
** start do loop to cycle thru list of emails;
** and create the site macro var for each loop;
%do i = 1 %to &cntemail;
%let email = %scan(&wantemail,&i,~);
%put This is for by group where email = &email;

**use site macro variable for file name and WHERE and TITLE;
ODS PDF (id=dagwood)
FILE="&outdir.PDF_&email..PDF" STARTPAGE=NO STYLE=Journal gtitle;
title j=center bold "Metrics for January 2020"; 
ods text= "Email: &email. ";
ods text= "Site: &site. ";
ods text= "Site Contact: &Site_Contact.";
ods text= "Enrolled this Month: &mcnt_enrolled. ";
run;
ods layout gridded columns=2;
ods graphics / width=300 height=300;
ods region;
ods pdf(id=dagwood) style=statdoc;
PROC SGPLOT DATA=work.samplereport;
where email = "&email";
Title "Enrollment Metrics for Your Site";
vbar site / response=Expected_Enrolled stat=sum DATALABEL LEGENDLABEL="Expected Enrollment for Your Site"
discreteoffset=-0.5 barwidth=0.2 fillattrs=graphdata2;
yaxis grid display=(nolabel);
xaxis display=(noline NOTICKS NOVALUES nolabel);
vbar site / response=site_enrolled_total stat=sum DATALABEL LEGENDLABEL="Enrolled for Your Site to Date"
discreteoffset=-0.2 barwidth=0.2 fillattrs=graphdata1;
yaxis grid display=(nolabel);
xaxis display=(noline NOTICKS NOVALUES nolabel);
vbar site / response=mcnt_enrolled stat=sum DATALABEL LEGENDLABEL="Enrolled this Month"
discreteoffset=0.2 barwidth=0.2 fillattrs=graphdata3;
yaxis grid display=(nolabel);
xaxis display=(noline NOTICKS NOVALUES nolabel);
vbar site / response=NetworkAvg stat=sum DATALABEL LEGENDLABEL="Your Network Avg Enrollment this Month"
discreteoffset=0.5 barwidth=0.2 fillattrs=graphdata4;
yaxis grid display=(nolabel);
xaxis display=(noline NOTICKS NOVALUES nolabel);
RUN;
ods region;
ods pdf(id=dagwood) style=statdoc;
goptions reset=all;
proc sgplot data=work.samplereport;
where email = "&email";
Title "Study-Wide Enrollment to Date and Goal";
vbar site / response=Overall_Study_Enrollment stat=sum DATALABEL LEGENDLABEL="Enrollment for All Sites"
discreteoffset=-0.3 barwidth=0.4 fillattrs=graphdata5;
yaxis grid display=(nolabel);
xaxis display=(noline NOTICKS NOVALUES nolabel);
vbar site / response=Overall_Expected_Study stat=sum DATALABEL LEGENDLABEL="Expected Enrollment Goal"
discreteoffset=0.3 barwidth=0.4 fillattrs=graphdata6;
yaxis grid display=(nolabel);
xaxis display=(noline NOTICKS NOVALUES nolabel);
run;
ODS LAYOUT END;
%end;
* get emails from DATA;
proc sql noprint;
select EMAIL into :EMAIL1- from &DATA;
select count(*) into :EMAILN from &DATA;
* cycle through emails;
%do I=1 %to &EMAILN;
filename temp email to="&&EMAIL&I"
attach="&outdir.PDF_&&email&I...PDF"
from="[e-mail]"
type="text/html"
subject="Site Metrics";
ods html file=temp;
run;
ods html close;
%end;
%mend getemail;
%getemail(samplereport);

Answer 1

电子邮件%DO循环语句未正确终止。

* cycle through emails;
%do I=1 %to &EMAILN

应该是

* cycle through emails;
%do I=1 %to &EMAILN;

也许ODS HTML CLOSE应该在循环之外。