I hate hard-coding variable lists in my SAS code. I love the special variable lists SAS provides, like name:, _numeric_, etc. But back in 2011, I wrote a post titled Jedi SAS Tricks - Building a Name Suffix variable list lamenting the lack of a special variable list in base SAS which would produce the variables ending with a particular text string. And, of course, I built a custom macro to do the deed for me.
My fellow SAS Jedi, Bruno Mueller of SAS Switzerland, just sent me what he modestly described as "a new version of the suffix macro". But it really was an awesome new approach that will completely replace the suffix macro in my personal autocall library! Bruno's varListPattern macro allows you to create variable lists for any SAS data set in-line with your code, using pattern search to define the list. I added a bit of code to make the macro self-documenting (use %varListMacro(!HELP) to produce syntax help in the SAS log). Because pattern matching can be a little tricky, I added a parameter that allows running the macro in "debug" mode for testing. In DEBUG mode the macro writes the variable list and some helpful debugging info to the SAS log instead of generating the list in-line with your SAS code.
To use the macro, first download the zip file for this post and unzip the two files it contains. Save the macro definition program varListPattern.sas to your autocall path (or, for one-time use or testing, just submit the program in your SAS session). The other file, TestingvarListPattern.sas, provides some examples of how to use the macro. In one of the examples, we write to the SAS log a list of numeric variables in SASHELP.MON1001 where the name has '9' as the second from the last character:
And the result:
NOTE: (VARLISTPATTERN) DSN=sashelp.mon1001 VARTYPE=N PATTERN=*9? NVARS=407 PRXPATTERN=.*9. VARLIST=S0390 S0391 S0393 S0394 S0395 S0396 S0397 S0398 S0490 S0491 S0492 S0493 S0495 S0496 S0497 S0498 S0499 S0590 S0591 S0592 S0593 S0594 S0595 S0596 S0597 S0598 S0599 S0690 S0692 S0694 S0695 S0697 S0698 S0699 S0790 S0791 S0792 S0793 S0794 S0795 S0796 S0798 S0799 S0897 S0898 S0899 S0990 S0991 S0992 S0993 S0994 S0995 S0997 S0998 S0999
In another example, we use the macro in-line within PROC PRINT to print the data set SASHELP.CLASS - but only those variables with 'E' as the second letter of the name!
title 'SASHELP.CLASS variables with E as the second letter'; proc print data=sashelp.class (obs=5); var %varListPattern(sashelp.class,?e*); run; title;
Now, that is one handy little macro! Thanks, Bruno, for sharing it with me - and now with everyone who reads this post :-)
Until next time, may the SAS be with you!