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;




Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.


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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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