BookmarkSubscribeRSS Feed
everyone
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 ('youremail@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();
2 REPLIES 2
Quentin
Super User

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;
The Boston Area SAS Users Group (BASUG) is hosting an in person Meeting & Training on June 27!
Full details and registration info at https://www.basug.org/events.
PaigeMiller
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

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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