How to send emails within a macro

[kmorrowvt]

I'm trying to send out custom emails to people on a list and getting the errors: 

ERROR: No logical assign for filename OUTPUTX.
ERROR: No body file. TAGSETS.MSOFFICE2K(EMAIL) output will not be created.

The code is:

options emailsys=smtp emailhost="exsvr2016.companyname.org";

%macro sheets (first, email);

data errors_&first;

  set errors (where=(firstname="&first"));

run;

FILENAME OUTPUTx EMAIL

SUBJECT = "Closeout T "

FROM = "<xxx@companyname.org>"

TO = "&email"

CT ='text/html';

ods tagsets.msoffice2k(id=email)

  file=OUTPUTx

  style=journal;

title1 "Closeout Report: filename.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;

data _null_;

  set dptlist;

call symputx ('first', firstname);

call symputx ('email', email);

  rc=dosubl('%sheets(&first)');

run;

Where the email is a single email.

This runs fine outside of a macro, but throws the errors once inside of a macro. 

[PaigeMiller]

When you get an error message in the log, we need to see the ENTIRE log for this section of code, not just the error messages. Since this is in a macro, you need to run this command first

options mprint;

then run your code again and copy and paste the log for this section of code into the window that appears when you click on the </> icon above where you type. 

Please show us the log for this section of code following the above instructions.

[kmorrowvt]

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

[ballardw]

Can you show the code that successfully sent an email BEFORE any macro variables or macro code was used?

[kmorrowvt]

Added above with the log for the macro portion.

[Patrick]

You've got certainly a timing issue in your last data step.

data _null_;
  set dptlist;
	call symputx ('first', firstname);
	call symputx ('email', email);
  rc=dosubl('%sheets(&first)');
run;

Macro variable &first will only be available after the data step (after the run;) and it will contain the value of firstname from the last row of source table dptlist. The same also applies for &email. 

I can't see a macro variable &email used in your macro even though you define it there as an input parameter.

First thing you need to make work is your macro called on it's own.

%sheets (<some value>, <some value>);

Once this macro works you can call it dynamically in a data step with code similar to below.

data _null_;
  set dptlist;
  length cmd $300;
  cmd=cats( '%sheets(',firstname,',',email,');') ;
  call execute(cmd);
run;

[kmorrowvt]

I missed the email in the dosubl, it's been added and it gets used as the "to" in the email.

I don't agree that I have a timing issue. It's grabbing the first name and email row by row and running through the macro as expected. 

[Patrick · Accepted solution]

Does the macro execute as desired if you just call it separately?

---

@kmorrowvt wrote:

I don't agree that I have a timing issue. It's grabbing the first name and email row by row and running through the macro as expected. 

---

Yes, you are right. There shouldn't be a timing issue. Tested via below code.

%macro test(param);
  data _null_;
    put "Value: &param";
  run;
%mend;

data test;
  value='abc'; output;
  value='999'; output;
  value='xyz'; output;
run;

data _null_;
  set test;
  call symputx('mvar',value);
  rc=dosubl('%test(&mvar);');
run;

If it was me then I'd still code as below using call execute().

data _null_;
  set test;
  cmd=cats('%test(',value,');');
  call execute(cmd);
run;

In my tests when using call execute() I also get a bit more logging info that shows me better what code the macro actually generates.

NOTE: CALL EXECUTE generated line.
1         + data _null_;     put "Value: abc";   run;

Value: abc

[kmorrowvt]

Yes, the macro is executing as expected. My only issue is getting it to dynamically send the emails. If I run the filename section outside the macro with the email recipient, the macro runs as expected, but obviously it only sends to the one email address defined outside the macro.

[Quentin]

What version of SAS do you have?  

If you call the macro twice (manually, so without DOSUBL or CALL EXECUTE), I assume it works and sends two emails?

For quick fix I would try using CALL EXECUTE instead of DOSUBL. 

This part of the log is interesting:

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.

Either the filename statement failed without generating an error message, or there is a weird DOSUBL problem where when you execute the FILENAME statement within the side session it can create a fileref but can't access it, or perhaps tagsets.msoffice2k is not happy writing to it for some reason.  It's very odd.

I would try making a much smaller reproducible example to test scenarios, because DOSUBL is interesting magik.

[kmorrowvt]

Yeah that seems to be it, the timing of compiling with dosubl and it's magic doesn't work, but it does with call execute.

[Quentin]

I have played with some small examples, and I have a theory.

I think when the ODS statement is executed within a DOSUBL block, it can't see a fileref that was created in the side session.  It can only see a fileref that exists in the main session.

If I run:

data _null_ ;
  rc=dosubl(
  'filename foo "%sysfunc(pathname(work))/foo.htm" ; data _null_ ; file foo ; put "hello" ;run ; filename foo clear ;' 
  ) ;
run ;

it works.  So the filename statement in side session is working, and FILE statement can use the fileref.

But if I run:

data _null_ ;
  rc=dosubl(
  'filename foo "%sysfunc(pathname(work))/foo.htm" ; ods html file=foo; proc print data=sashelp.class ;run ;ods html close ; filename foo clear ;' 
  ) ;
run ;

It errors:

162  data _null_ ;
163    rc=dosubl(
164    'filename foo "%sysfunc(pathname(work))/foo.htm" ; ods html file=foo; proc print data=sashelp.class ;run ;ods html close ;
164! filename foo clear ;'
165    ) ;
166  run ;

NOTE: Writing HTML Body file: FOO
ERROR: No logical assign for filename FOO.
ERROR: No body file. HTML output will not be created.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.

NOTE: Fileref FOO has been deassigned.
NOTE: The SAS System stopped processing this step because of errors.

It looks like the fileref is successfully created and cleared, but the fleref exists in the side-session only, not main session.  When the ODS statement executes, it's clearly not seeing the side-session fileref.  Which explains why if you create the fileref in the main session, the ODS statement works :

filename foo "%sysfunc(pathname(work))/foo.htm" ;

data _null_ ;
  rc=dosubl(
  'ods html file=foo; proc print data=sashelp.class ;run ;ods html close ;' 
  ) ;
run ;

filename foo clear ;

DOSUBL is actually working really nicely.  For example, if you use the FILENAME statement within the DOSUBL side session to clear a fileref that exists in the main session, it will error and tell you it's a scope error :

136  filename foo "%sysfunc(pathname(work))/foo.htm" ;
137
138  data _null_ ;
139    rc=dosubl(
140    'ods html file=foo; proc print data=sashelp.class ;run ;ods html close ; filename foo clear ;'
141    ) ;
142  run ;

NOTE: Writing HTML Body file: FOO
NOTE: There were 19 observations read from the data set SASHELP.CLASS.

ERROR: Logical name FOO assigned but not in current scope.
ERROR: Error in the FILENAME statement.

That's a great error message!

Bottom line, I think it's a bug / unwanted behavior of the ODS statement that when run in a DOSUBL side session, it can't see filerefs that exist in the side session.

I would switch to CALL EXECUTE.  Or if you really want to use DOSUBL, you could change your ODS step to send the results to a file, instead of the the fileref.  Then you can use a step like:

data _null_;
  file outputx; *your filreref point to email;
  infile "...\tempfile.htm"; *temp file with html results;
  input;
  put _infile_;
run;

to send the email.  Because this problem seems to be specific to the ODS statement.

Tagging @yabwon  curious if you agree this looks like a problem with the ODS statement in side-session not being able to see side-session filerefs.

[yabwon]

Agree it looks like ODS "issue". 

Fileref in "standard" data step works good:

1    data _null_ ;
2      rc=dosubl(
3      'filename fooBar "%sysfunc(pathname(work))/foo.txt" ;
4        data _null_;
5          file fooBar;
6          x = "ABC";
7          put x x x;
8        run;
9        filename fooBar clear ;
10       '
11     ) ;
12   run ;

NOTE: The file FOOBAR is:
      Filename=*****************\foo.txt,
      RECFM=V,LRECL=32767,File Size (bytes)=0,
      Last Modified=17Nov2023:16:01:02,
      Create Time=17Nov2023:16:00:30

NOTE: 1 record was written to the file FOOBAR.
      The minimum record length was 11.
      The maximum record length was 11.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


NOTE: Fileref FOOBAR has been deassigned.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds

Bart

[yabwon]

One more example that "it is ODS fault" goes here:

1    options
2      emailsys=smtp
3      emailhost=("*******************" SSL)
4      emailport = 465
5      emailid="************************"
6      emailpw=XXXXXXXXXXXXXXXXXXXXX
7      emailauthprotocol=PLAIN
8    ;

9    data _null_ ;
10     rc=dosubl(
11     'filename fooBar email
12     "xxx@mail.com"
13      subject="A test"
14     ;
15       data _null_;
16         file fooBar;
17         x = "ABC";
18         put x x x;
19       run;
20       filename fooBar clear ;
21       '
22     ) ;
23   run ;

NOTE: The file FOOBAR is:
      E-Mail Access Device

Message sent
      To:          "xxx@mail.com"
      Cc:
      Bcc:
      Subject:     A test
      Attachments:
NOTE: 1 record was written to the file FOOBAR.
      The minimum record length was 11.
      The maximum record length was 11.
NOTE: DATA statement used (Total process time):
      real time           1.36 seconds
      cpu time            0.07 seconds


NOTE: Fileref FOOBAR has been deassigned.
NOTE: DATA statement used (Total process time):
      real time           1.38 seconds
      cpu time            0.09 seconds

Bart

[yabwon]

Even SASHELP.VEXTFL view recognise change in filerefs for main[foo1.txt] and DoSubL[foo2.txt] sessions:

1    filename fooBar "%sysfunc(pathname(work))/foo1.txt" ;
2    data _null_;
3      set sashelp.vextfl;
4      where upcase(fileref)  = "FOOBAR";
5      put xpath=;
6    run;

xpath=R:\_TD23928_YABWONL5P_\foo1.txt
NOTE: There were 1 observations read from the data set SASHELP.VEXTFL.
      WHERE UPCASE(fileref)='FOOBAR';
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


7
8
9    data _null_ ;
10     rc=dosubl(
11     'options ps=min;
12      filename fooBar "%sysfunc(pathname(work))/foo2.txt" ;
13       data _null_;
14         set sashelp.vextfl;
15         where upcase(fileref)  = "FOOBAR";
16         put xpath=;
17       run;
18       '
19     ) ;
20   run ;



xpath=R:\_TD23928_YABWONL5P_\foo2.txt
NOTE: There were 1 observations read from the data set SASHELP.VEXTFL.
      WHERE UPCASE(fileref)='FOOBAR';
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


21
22   data _null_;
23     set sashelp.vextfl;
24     where upcase(fileref)  = "FOOBAR";
25     put xpath=;
26   run;

xpath=R:\_TD23928_YABWONL5P_\foo1.txt
NOTE: There were 1 observations read from the data set SASHELP.VEXTFL.
      WHERE UPCASE(fileref)='FOOBAR';
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Bart