topic Re: Getting a list of variables in a table in a single statement in SAS Programming

Macro function to return a list of variables in a table in a single statement : %ut_varlist

DavePrinsloo — Sat, 21 Mar 2020 21:30:53 GMT

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;

Re: Getting a list of variables in a table in a single statement

PaigeMiller — Fri, 20 Mar 2020 11:28:04 GMT

proc contents data=have; run;

Re: Getting a list of variables in a table in a single statement

DavePrinsloo — Fri, 20 Mar 2020 11:38:31 GMT

Thats not the point! I cannot use Proc contents inside a macro statement!

Re: Macro function to return a list of variables in a table in a single statement : %ut_varlist

Tom — Fri, 20 Mar 2020 12:20:42 GMT

Can you edit your post and move the examples into SAS code block so that formatting is preserved?

Re: Macro function to return a list of variables in a table in a single statement : %ut_varlist

data_null__ — Fri, 20 Mar 2020 12:26:17 GMT

DOSUBL Function

Imports macro variables from the calling environment, and exports macro variables back to the calling environment.

Re: Macro function to return a list of variables in a table in a single statement : %ut_varlist

Quentin — Fri, 20 Mar 2020 12:39:59 GMT

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

Re: Macro function to return a list of variables in a table in a single statement : %ut_varlist

DavePrinsloo — Fri, 20 Mar 2020 13:32:10 GMT

I agree DOSUBL is a great feature! This code has accompanied me long before it was available. I have not tried nested calls as supported by %ut_varlist

Re: Macro function to return a list of variables in a table in a single statement : %ut_varlist

DavePrinsloo — Fri, 20 Mar 2020 13:34:36 GMT

done. Thanks for the heads-up

Re: Macro function to return a list of variables in a table in a single statement : %ut_varlist

Quentin — Fri, 20 Mar 2020 14:28:48 GMT

DOSUBL seems to manage nested calls well. Meaning DOSUBL code can call DOSUBL, and you end up with a side-session that has a side-session.
And agree, even thought I've become a fan of DOSUBL, my %Varlist macro stilll uses the SCL approach as well, and no point to changing it and revalidating etc.

Re: Macro function to return a list of variables in a table in a single statement : %ut_varlist

DavePrinsloo — Sat, 21 Mar 2020 19:09:37 GMT

The called macro ut_fmt2type has been replaced by code suggested by "Tom"