BookmarkSubscribeRSS Feed
Fluorite | Level 6

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;

	%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 ('')
		Subject='Empty Table Alert';
		FILE Mailbox;
		put "&test";
		PUT "The following tables are empty";
		put &empty_tables;
Lapis Lazuli | Level 10 svh
Lapis Lazuli | Level 10
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;
Diamond | Level 26

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.

Paige Miller
Super User
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.
Super User

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

%let empty_tables=a;

data _null_;
put &empty_tables.; run;


233  %let empty_tables=a;
235  data _null_;
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;
241  data _null_;
243     put "&empty_tables.";
244  run;





Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 5 in conversation