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*/
/*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;
%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 ('firstname.lastname@example.org')
Subject='Empty Table Alert';
PUT "The following tables are empty";
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.