Dynamic Macros

[vnreddy]

Hi,

Could someone help me how can i use the required_tables macro in where statement.

At present i have used it manually, but in future required tables might changes so i want to use the macro in where statement.

Basically i am trying to see two datasets info.

%let required_tables=
	Accoun_tbl Payments_tbl
;
libname lib "/sas/UAT/Data/";

PROC SQL;
	create table temp as
		SELECT	memname,
			nobs,
			nvar,
			filesize
		FROM DICTIONARY.TABLES
			WHERE MEMTYPE = 'DATA' and memname IN ('Accoun_tbl' , 'Payments_tbl')
				AND LIBNAME = "LIB";
QUIT;

Could someone help me with about query.

Thanks,

vnreddy

[Tom · Accepted solution]

I think this question has come up before.  There are some macro "functions" that users have created to convert a delimited list of values into a quoted list of values.  For example:  %qlist() 

 memname IN %qlist(&required_tables)

But you don't really need it for this.  You could use INDEXW() or FINDW() function instead.

indexw("&required_tables",trim(memname),' ')

PS:  %qlist is a macro.  &required_tables is a macro variable or if you want a one word name you can call it a symbol.

[vnreddy]

Thank you it worked.

[Reeza]

Terminology is important. You're working with macro variables here - not a macro.

Macro variables can be treated initially as simple find and replace. 

So your code will become:

%let required_tables= Accoun_tbl Payments_tbl;
libname lib "/sas/UAT/Data/";

PROC SQL;
	create table temp as
		SELECT	memname,
			nobs,
			nvar,
			filesize
		FROM DICTIONARY.TABLES
			WHERE MEMTYPE = 'DATA' and memname IN (&required_tables)
				AND LIBNAME = "LIB";
QUIT;

But that will generate a line as follows - replacing the macro variable with your value. This is not valid SAS code. It needs the quotes at minimum, the comma ideally. 

WHERE MEMTYPE = 'DATA' and memname IN (Accoun_tbl Payments_tbl)

Can you create the macro variable with the quotes and comma? Then what happens?

%let required_tables= 'Accoun_tbl' , 'Payments_tbl';

Does that work for you as a solution?

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

[vnreddy]

Thank you

[FreelanceReinh]

Hi @vnreddy,

I think you only find uppercase member names in DICTIONARY.TABLES. So, you can use this condition:

memname IN ("%sysfunc(tranwrd(%upcase(&required_tables),%str( )," "))")

(assuming that the table names don't contain blanks).