Sorry, this is the code:
%let path=\\burlington.vtoxford.org\corp\stats\;
%include "&path.include\sas_libnames.sas";
options mprint;
data _null;
year=year(datepart(datetime()));
month=month(datepart(datetime()));
call symput ('year', strip(year));
call symput ('month', strip(month));
run;
%macro years;
%global lastyr clsyr archiveyr nextyr;
%if &month gt 6 %then
%do;
%let lastyr=%eval(&year-1);
%let clsyr=&year;
%end;
%else
%do;
%let lastyear=%eval(&year-2);
%let clsyr=%eval(&year-1);
%end;
%let archiveyr=%eval(&year-3);
%let nextyr=%eval(&clsyr+1);
%mend years;
%years;
data center (keep=hospno hospname dptcontact phistartyear);
set prdcen.tblcenter (where=(dataact=1 and hospno ne 999));
run;
proc sort data=center;
by dptcontact;
run;
proc sort data=prdvon.tblLUDPTContact out=contact;
by dptcontactid;
run;
data center;
merge center (in=a) contact (keep=dptcontactid firstname email rename=(dptcontactid=dptcontact));
by dptcontact;
if a;
drop dptcontact;
if firstname='Erika P' then firstname='Erika';
run;
data groups (keep=groupid hospno inactivedate);
set prdcen.tbllnkcentersgroups (where=(groupid=1 and inactivedate ge datetime()));
run;
*Closed Out;
data closeout (keep=hospno closeoutyear ConfirmationRcvd qmrready datafinalized rename=(closeoutyear=yob));
set prdvon.tblqmrcloseout (where=(&archiveyr le closeoutyear le &clsyr));
run;
proc sort data=closeout;
by hospno yob;
run;
proc sort data=center;
by hospno;
run;
proc sort data=groups;
by hospno;
run;
data errors;
set prdvon.tblinfanterrors (keep=hospno id byear fieldname errormessage errortype
where=(&archiveyr le yob le &clsyr and errortype in ('E','B'))
rename=(byear=yob));
run;
proc sort data=errors;
by hospno yob;
run;
data errors;
merge closeout (where=(datafinalized=-1) in=a) errors (in=b);
by hospno yob;
if a and b;
run;
data errors;
merge center errors (in=a) groups (keep=hospno in=b);
by hospno;
if a;
if b then cpqcc=1;
else cpqcc=0;
run;
proc sort data=errors;
by yob hospno;
run;
proc summary data=errors nway;
class hospno hospname yob id firstname;
output out=error_count;
run;
proc summary data=error_count nway;
class hospno hospname yob firstname;
output out=error_count;
run;
proc sort data=error_count;
by yob hospno;
run;
ods listing close;
options emailsys=smtp emailhost="exsvr2016.burlington.vtoxford.org";
%macro sheets (first, email);
data errors_&first;
set errors (where=(firstname="&first"));
run;
FILENAME OUTPUTx EMAIL
SUBJECT = "Closeout T "
FROM = "<xxx@company.org>"
TO = "&email"
CT ='text/html';
ods tagsets.msoffice2k(id=email)
file=OUTPUTx
style=journal;
title1 "Closeout Report: file.xlsx";
title2 "Errors in closed out years";
footnote1 "This is an automatically generated email. Please do not respond to this email.";
proc report data=error_count (where=(firstname="&first")) nofs;
columns hospno hospname yob _FREQ_;
define hospno/"Center" order order=internal display;
define hospname/"Hospital" display;
define yob/"Year" display;
define _FREQ_/"Infants";
compute _FREQ_;
length svar $50;
if yob=&archiveyr then
do;
svar='style=[backgroundcolor=lightred]';
end;
else do svar='style=[backgroundcolor=white]';
end;
call define (_row_,'style',svar);
endcomp;
run;
quit;
ods tagsets.msoffice2k(id=email) close;
%mend sheets;
proc summary data=errors nway;
class firstname email;
output out=dptlist;
run;
data _null_;
set dptlist;
call symputx ('first', firstname);
call symputx ('email', email);
rc=dosubl('%sheets(&first, &email)');
run;
And this is the log for the macro call:
1513 data _null_;
1514 set dptlist (where=(firstname='Amy'));
1515 call symputx ('first', firstname);
1516 call symputx ('email', email);
1517 rc=dosubl('%sheets(&first)');
1518 run;
MPRINT(SHEETS): data errors_Amy;
MPRINT(SHEETS): set errors (where=(firstname="Amy"));
MPRINT(SHEETS): run;
NOTE: There were 1100 observations read from the data set WORK.ERRORS.
WHERE firstname='Amy';
NOTE: The data set WORK.ERRORS_AMY has 1100 observations and 14 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(SHEETS): FILENAME OUTPUTx EMAIL SUBJECT = "Closeout T " FROM = "<xxx@company.org>" TO =
"xxx@company.org" CT ='text/html';
MPRINT(SHEETS): ods tagsets.msoffice2k(id=email) file=OUTPUTx style=journal;
NOTE: Writing TAGSETS.MSOFFICE2K(EMAIL) Body file: OUTPUTX
ERROR: No logical assign for filename OUTPUTX.
ERROR: No body file. TAGSETS.MSOFFICE2K(EMAIL) output will not be created.
MPRINT(SHEETS): title1 "Closeout Report:
file.xlsx";
MPRINT(SHEETS): title2 "Errors in closed out years";
MPRINT(SHEETS): footnote1 "This is an automatically generated email. Please do not respond to this
email.";
MPRINT(SHEETS): proc report data=error_count (where=(firstname="Amy")) nofs;
MPRINT(SHEETS): columns hospno hospname yob _FREQ_;
MPRINT(SHEETS): define hospno/"Center" order order=internal display;
MPRINT(SHEETS): define hospname/"Hospital" display;
MPRINT(SHEETS): define yob/"Year" display;
MPRINT(SHEETS): define _FREQ_/"Infants";
MPRINT(SHEETS): compute _FREQ_;
MPRINT(SHEETS): length svar $50;
MPRINT(SHEETS): if yob=2020 then do;
MPRINT(SHEETS): svar='style=[backgroundcolor=lightred]';
MPRINT(SHEETS): end;
MPRINT(SHEETS): else do svar='style=[backgroundcolor=white]';
MPRINT(SHEETS): end;
MPRINT(SHEETS): call define (_row_,'style',svar);
MPRINT(SHEETS): endcomp;
MPRINT(SHEETS): run;
NOTE: There were 41 observations read from the data set WORK.ERROR_COUNT.
WHERE firstname='Amy';
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
MPRINT(SHEETS): quit;
MPRINT(SHEETS): ods tagsets.msoffice2k(id=email) close;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set WORK.DPTLIST.
WHERE firstname='Amy';
NOTE: DATA statement used (Total process time):
real time 0.50 seconds
cpu time 0.34 seconds
... View more
