BookmarkSubscribeRSS Feed
everyone
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;
		%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();
4 REPLIES 4
svh
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;
PaigeMiller
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
Reeza
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.
ballardw
Super User

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

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1733 views
  • 0 likes
  • 5 in conversation