topic Re: Dynamic Macros in SAS Programming

Dynamic Macros

vnreddy — Wed, 14 Apr 2021 16:03:16 GMT

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

Re: Dynamic Macros

Tom — Wed, 14 Apr 2021 16:19:28 GMT

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.

Re: Dynamic Macros

Reeza — Wed, 14 Apr 2021 16:18:13 GMT

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-Appendix/ta-p/291716

Re: Dynamic Macros

FreelanceReinh — Wed, 14 Apr 2021 16:37:22 GMT

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

Re: Dynamic Macros

vnreddy — Wed, 14 Apr 2021 16:24:26 GMT

Thank you it worked.

Re: Dynamic Macros

vnreddy — Wed, 14 Apr 2021 16:28:05 GMT

Thank you