BookmarkSubscribeRSS Feed
Fluorite | Level 6

I've created a macro to check the observation counts of tables and include the names of any empty tables inside a macro variable. Inside of the macro, I'm trying to email myself a message detailing which, if any, tables are empty. However, the value of my macro variable does not get included in my email. What am I doing wrong?

I've included a reprex below:

/*list of table names*/
%let tables = a b c;

/*row counts corresponding to table names in &tables*/
%let table_row_counts = 0 100 500;

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

/*check to see if tables are empty, if any are, then include email with the names of the empty tables*/
%macro validate();

/*test macro variable. this macro var is properly 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"*/ /*email will display the value of &test, but not &empty_tables (value should be displayed as "a"*/ %if &total_empty_tables > 0 %then %do; FILENAME Mailbox EMAIL ('') Subject='Empty Table Alert'; DATA _NULL_; FILE Mailbox; put "&test"; PUT "The following tables are empty"; put &empty_tables; RUN; %return; %end; %mend; %validate();

Suggest running the macro with MPRINT on and checking the log.  Do you get any errors or bad notes?  I would think it would be throwing unititialized variable notes.


I noticed:

%let empty_tables = %scan(&tables,&i) &empty_tables;

Your comment says:

%put &empty_tables; /*resolves to "a"*/

But from the code you posted, I don't see anything that will add quotation marks.  Suggest trying:

%let empty_tables = "%scan(&tables,&i)" &empty_tables;
Check out the Boston Area SAS Users Group (BASUG) video archives:
Diamond | Level 26

You have already asked this question and received a number of valuable answers, including one answer and a second answer both of which I regard as correct.

Paige Miller



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
  • 2 replies
  • 1 like
  • 3 in conversation