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:
- It can susbset the selected columns that contain a specified substring as a prefix, suffix, or anywhere in the name.
- on the column type (NUM, CHAR; DATE, DATETIME, etc)
- It can add prefixes to each column name
- The default delimiter is blank, but other delimiter can be specified. Commas are useful when generating SQL code!
- It can double or single quote each column name column in the returned list
- It can limit the returned list to be one of the items in a SELECT list. This is useful in nested calls that find common columns or in checking that a specific list of columns are all contained in the table,
- It can exclude columns in an EXCLUDE list.
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;
Click image to register for webinar
