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();
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;
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.
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.
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.