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 ('firstname.lastname@example.org') Subject='Empty Table Alert'; DATA _NULL_; FILE Mailbox; put "&test"; PUT "The following tables are empty"; put &empty_tables; RUN; %return; %end; %mend; %validate();
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.
Let's reduce the complexity of the code for clarity:
%let empty_tables=a; data _null_;
put &empty_tables.; run;
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
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.