Macro function to return a list of variables in a table in a single statement
This post describes and attaches %ut_varlist, a useful utility macro that I use in many SAS programs. An often used way to get a list of columns is to query the SAS data dictionary view SASHELP.VCOLUMN or to use Proc contents to output a SAS table.
However, this posting presents a macro function that does not generate executable code and can therefore be used in expressions and even allows nested calls. For example, to return the list of character columns that are both in SASHELP.CLASS and SASHELP.CLASSFIT, the following calls can be made:
%let common_vars =%ut_varlist(table=sashelp.class,select=%ut_varlist(table=sashelp.classfit));
The ability to use %ut_varlist enables SAS programmers to write code that would otherwise be more complex and need more time to debug and maintain.
I present a list of usage examples that show the versatility of %ut_varlist.
Addtional features:
The attached files include the source code needed as well as a word document.
As this is my first blog after using SAS for decades, I welcome feedback!
Example 1 : select all variables in the table;
%let classfit_vars=%ut_varlist(table=sashelp.classfit);
%put &=classfit_vars;
Example 2 : character variables in the table;
%let char_vars=%ut_varlist(table=sashelp.classfit, type=CHAR);
%put &char_vars.;
Example 3 : comma-separated and quoted_vars;
%let comma_quoted_vars=%ut_varlist(table=sashelp.class, newdlm=COMMA, quote=YES);
%put &=comma_quoted_vars;
Example 4 : comma-separated single-quoted_vars;
%let NUM_comma_singlequotes=%ut_varlist(table=sashelp.cars, type=NUM, newdlm=COMMA, quote=SINGLE);
%put &=NUM_comma_singlequotes.;
Example 5 - Keep all cols in the main input table = Automatically drop temp cols.;
data test2;
set sashelp.cars;
keep %ut_varlist(table=sashelp.cars);
temp_var = 1; * does not need to be dropped ! ;
run;
Example 6a - Use to check existence of a column in a table ;
%let my_table = sashelp.classfit;
%let my_col=lower;
%if %ut_varlist(table=&my_table., select=&mycol.) ne %str() %then %do;
%put Column "&mycol." exists in table &my_table.;
%end;
* Example 6b - Use to check existance of 2 columns in a table
We don't care of the order they appear in the table ;
%let my_table = sashelp.classfit;
%if %length(%ut_varlist(table=&my_table., select=lower upper)) = %length(lower upper) %then %do;
%put Columns "lower" and "upper" exist in table &my_table.;
%end;
* Example 6c -Use to identify columns that are not in another table
- first example of nested call;
%let missing_cols = %ut_varlist(table=sashelp.classfit,
exclude = %ut_varlist(table=sashelp.class));
%if %length(&missing_cols.) ne 0 %then %do;
%put NOTE: sashelp.class does not have the columns in sashelp.classfit:
&missing_cols. ;
%end;
Example 7 - Use to return cols common to two tables;
%let common_vars = %ut_varlist(table=sashelp.class,select=%ut_varlist(table=sashelp.classfit));
%put common_vars=&common_vars;
Example 8 - Generic Join on common columns on tables sashelp.class and sashelp.classfit.
Note that columns in class and classfit should not be re-selected from classfit.
options mprint;
proc sql noprint;
create table class_join
as select %ut_varlist(table=sashelp.class, add_prefix=class., newdlm=COMMA),
%ut_varlist(table=sashelp.classfit, add_prefix=fit., newdlm=COMMA,
exclude = %ut_varlist(table=sashelp.class, add_prefix=fit.))
from sashelp.class class
left join
sashelp.classfit fit
on class.name=fit.name;
quit;
Example 9 - format all numeric columns that contain _AMT in the name with format COMMA7.2 ;
data pocket_money;
set sashelp.class;
month_pocket_amt=age*5.65;
annual_amt = 12 * month_pocket_amt;
run;
* reformat _AMT fields to use comma format;
data comma_fmt;
set pocket_money;
format %ut_varlist(table=pocket_money, contain=_AMT, type=NUM, contain_pos=END) COMMA7.2;
run;
Example 10 - force consistent date and datetime formats to the relevant columns in a table ;
* first create a dummy table with differing formats ;
data multiple_date_fmts;
today = today();
format today date7.;
dt = datetime();
my_amt = 12.34;
my_int = round(my_amt);
b8601dt = dt; b8601DN = dt; dtdate = dt;
b8601da = today; ddmmyyp=today; ddmmyyn=today;
time = timepart(today());
format time time8.;
format dt datetime. b8601dt b8601dt. b8601DN b8601DN. dtdate dtdate9. ;
format b8601da b8601da. ddmmyyp ddmmyyp10. ddmmyyn ddmmyyn8.;
notamt_not_relevant = 1;
put _all_;
run;
%let datecols=%ut_varlist(table=multiple_date_fmts, type=DATE);
%let datetimecols=%ut_varlist(table=multiple_date_fmts, type=DATETIME);
proc datasets library=work nolist;
modify multiple_date_fmts;
format &datecols. yymmdd10.;
format &datetimecols. datetime19.2;
quit;
Example 11 - Create a macro to generate a rename option to add a prefix for all columns that do not already have that prefix ;
%macro rename_cols_option(inTab=, new_prefix=);
%local old_names new_names vi;
%let old_names = %ut_varlist(table=&inTab.
, exclude=%ut_varlist(table=&inTab,contain=&new_prefix.
, contain_pos=START));
%let new_names = %ut_varlist(table=&inTab, add_prefix=&new_prefix.,
exclude=%ut_varlist(table=&inTab,contain=&new_prefix.,
contain_pos=START));
RENAME = (
%do vi=1 %to %sysfunc(countw(&old_names.));
%scan(&old_names.,&vi.) = %scan(&new_names.,&vi.)
%end;
)
%mend rename_cols_option;
* example call of rename_cols_option;
data helpcars;
set sashelp.cars(%rename_cols_option(inTab=sashelp.cars, new_prefix=sash_));
run;
proc contents data=have; run;
DOSUBL Function
Imports macro variables from the calling environment, and exports macro variables back to the calling environment.
I'm a big fan of function-style macros like this.
I've been playing with DOSUBL, one of the cool things it allows is to run full steps (PROC steps, DATA steps, etc) in a function-style macro, e.g.:
%macro ExpandVarList(data) ;
%local rc varnames ;
%let rc = %sysfunc(dosubl(%nrstr(
proc contents data=&data out=__ExpandVarList(keep=name) noprint ;
run ;
proc sql noprint ;
select name into :varnames separated by ' '
from __ExpandVarList ;
drop table __ExpandVarList ;
quit ;
)));
&varnames /*return*/
%mend ;
%put %expandvarlist(sashelp.class) ;
%put %expandvarlist(sashelp.class(keep=_numeric_)) ;
I've got a good handful of utility macros that use %SYSFUNC with the SCL functions to open data etc. But I'm planning to use the DOSUBL approach in the future. There is a performance price to DOSUBL, but I think it's worth it for code simplification.
John King has a great paper on using DOSUBL in function-style macro, which also shows a %Varlist macro:
http://www.mwsug.org/proceedings/2017/BB/MWSUG-2017-BB142.pdf
And of course the foundational DOSUBL paper is Rick Langston's:
https://support.sas.com/resources/papers/proceedings13/032-2013.pdf
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.