topic Re: How to include macro variable in an email in SAS Programming

How to include macro variable in an email

everyone — Wed, 10 May 2023 21:00:07 GMT

I've created a macro that creates a macro variable containing the names of tables with 0 observations. Within this macro, I also send an email if any tables are empty/have 0 observations. For some reason, the value of the macro variable containing name of empty tables isn't being included in my email. The email is sent, but the macro var's value isn't included in the email. What am I doing wrong?

Reprex with notes/comments below:

/*include names of tables to check*/
%let tables = a b c;

/*the corresponding row counts for the tables in &tables*/
%let table_row_counts = 0 100 500;

%let number_of_tables = %sysfunc(countw(&tables));

/*Check each table's row counts, if any table has 0 rows, then include table name in &empty_tables*/
%macro validate();

        /*Test macro -- this is sucessfully included in email*/
	%let test = test message;

	%let empty_tables = ;

	%do i = 1 %to &number_of_tables;
		%if %scan(&table_row_counts,&i) = 0 %then %do;
			%let empty_tables = %scan(&tables,&i) &empty_tables;
		%end;
	%end; 

	%let total_empty_tables = %sysfunc(countw(&tables));
	%put &empty_tables; /*Resolves to "a" here, but is missing in email sent below*/

	%if &total_empty_tables > 0 %then %do;
		* Email saying module has failed;
		FILENAME Mailbox EMAIL ('your_email@email.com')
		Subject='Empty Table Alert';
		DATA _NULL_;
		FILE Mailbox;
		put "&test";
		PUT "The following tables are empty";
		put &empty_tables;
		RUN;
	%return;
	%end;
%mend;
%validate();

Re: How to include macro variable in an email

svh — Wed, 10 May 2023 20:26:31 GMT

Just a quick guess...but should it be %put since you are evaluating the macro variable in your email message?

/*revised line at bottom of your code*/
%put &empty_tables;

Re: How to include macro variable in an email

PaigeMiller — Wed, 10 May 2023 21:36:42 GMT

Your code:

		put "&test";
		PUT "The following tables are empty";
		put &empty_tables;

What is the difference between the first line I quoted above, and the last line I quoted above? If you can answer that, I think you can fix your code yourself.

By the way, please heed Maxim 2 — read the log, which clearly would indicate you have done something wrong.

Re: How to include macro variable in an email

Reeza — Wed, 10 May 2023 21:43:25 GMT

You ticked the "no solution needed for this topic" when you created the post. Is that accurate? If not, you can click the three lines beside the post to edit it and remove that option.

Re: How to include macro variable in an email

ballardw — Wed, 10 May 2023 21:45:49 GMT

Let's reduce the complexity of the code for clarity:

%let empty_tables=a;

data _null_;

   put &empty_tables.;
run;

Log:

233  %let empty_tables=a;
234
235  data _null_;
236
237     put &empty_tables.;
238  run;

NOTE: Variable a is uninitialized.

The PUT statement resolves to : put a;

Which is used in a manner that SAS thinks is a variable.

You want quotes around your macro variable so it is treated as a text value that Put understands.

239  %let empty_tables=a;
240
241  data _null_;
242
243     put "&empty_tables.";
244  run;

a