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
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.
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.
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...
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).
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.