topic Re: How to send emails within a macro in SAS Programming

How to send emails within a macro

kmorrowvt — Thu, 16 Nov 2023 13:58:55 GMT

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.

Re: How to send emails within a macro

PaigeMiller — Thu, 16 Nov 2023 15:08:42 GMT

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.

Re: How to send emails within a macro

ballardw — Thu, 16 Nov 2023 15:10:59 GMT

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

Re: How to send emails within a macro

kmorrowvt — Thu, 16 Nov 2023 22:34:52 GMT

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

Re: How to send emails within a macro

kmorrowvt — Thu, 16 Nov 2023 16:25:10 GMT

Added above with the log for the macro portion.

Re: How to send emails within a macro

Patrick — Thu, 16 Nov 2023 22:28:17 GMT

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;

Re: How to send emails within a macro

kmorrowvt — Thu, 16 Nov 2023 22:37:33 GMT

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.

Re: How to send emails within a macro

Patrick — Thu, 16 Nov 2023 23:21:03 GMT

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

Re: How to send emails within a macro

kmorrowvt — Fri, 17 Nov 2023 12:17:53 GMT

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.

Re: How to send emails within a macro

Quentin — Fri, 17 Nov 2023 13:29:51 GMT

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.

Re: How to send emails within a macro

kmorrowvt — Fri, 17 Nov 2023 14:27:10 GMT

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.

Re: How to send emails within a macro

Quentin — Fri, 17 Nov 2023 14:45:04 GMT

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.

Re: How to send emails within a macro

yabwon — Fri, 17 Nov 2023 15:03:08 GMT

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

Re: How to send emails within a macro

yabwon — Fri, 17 Nov 2023 15:23:40 GMT

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

Re: How to send emails within a macro

yabwon — Fri, 17 Nov 2023 15:26:26 GMT

The call execute() is "in the same session" as all ODS references so there is no confusion "where to look for reference".

The only thing I would add to the workaround code would be %NRSTR() wrapper, to ensure that 4GL and macro code will run in proper timing:

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

Bart

Re: How to send emails within a macro

yabwon — Fri, 17 Nov 2023 15:38:45 GMT

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

Re: How to send emails within a macro

yabwon — Fri, 17 Nov 2023 15:49:21 GMT

The pathname() function also recognises reference correctly, what may give some workaround for the issue:

data _null_ ;
  rc=dosubl(
  '
  filename foo "/path/to/some/directory/" ; 

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

Bart

Re: How to send emails within a macro

Quentin — Fri, 17 Nov 2023 15:54:01 GMT

Thanks @yabwon , looking at SASHELP.VEXTFL is really nice.

Everything seems to be working really well in DOSUBL. The side session can see filerefs from the main session, and if you create a side session fileref it will see that one, etc.

It just seems like a problem with ODS statement not being able to see the side-session filerefs. I guess some "part" of the ODS statement is executing in the main session.

But as you show, if you can avoid using a fileref on the ODS statement, all is well.

Re: How to send emails within a macro

yabwon — Fri, 17 Nov 2023 15:58:18 GMT

One more example (with PDF) now:

1    data _null_ ;
2      rc=dosubl(
3      '
4      filename foo "%sysfunc(pathname(work))/foo123.pdf" ;
5
6      ods pdf file="%sysfunc(pathname(foo))"
7        ;
8      proc print data=sashelp.class ;run ;
9      ods pdf close ;
10     filename foo clear ;'
11     ) ;
12   run ;

NOTE: Writing ODS PDF output to DISK destination "R:\_TD23484_YABWONL5P_\foo123.pdf", printer "PDF".
NOTE: This SAS session is using a registry in WORK.  All changes will be lost at the end of this
      session.
NOTE: Writing HTML Body file: sashtml.htm
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds


NOTE: ODS PDF printed 1 page to R:\_TD23484_YABWONL5P_\foo123.pdf.
NOTE: Fileref FOO has been deassigned.
NOTE: DATA statement used (Total process time):
      real time           0.11 seconds
      cpu time            0.11 seconds


13
14   data _null_ ;
15     rc=dosubl(
16     '
17     filename bar "%sysfunc(pathname(work))/bar123.pdf" ;
18
19     ods pdf file=bar
20       ;
21     proc print data=sashelp.class ;run ;
22     ods pdf close ;
23     filename bar clear ;'
24     ) ;
25   run ;

ERROR: Unable to resolve fileref BAR. ODS PDF initialization fails.
ERROR: Logical name is not available.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


NOTE: Fileref BAR has been deassigned.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.07 seconds
      cpu time            0.03 seconds

Bart