topic Re: Including macro variable in email in SAS Programming

Including macro variable in email

everyone — Wed, 10 May 2023 15:30:18 GMT

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

Re: Including macro variable in email

Quentin — Sun, 14 May 2023 17:32:57 GMT

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;

Re: Including macro variable in email

PaigeMiller — Sun, 14 May 2023 18:48:01 GMT

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.