BookmarkSubscribeRSS Feed
DavePrinsloo
Pyrite | Level 9

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;

 

 

 

 

 

9 REPLIES 9
PaigeMiller
Diamond | Level 26
proc contents data=have; run;
--
Paige Miller
DavePrinsloo
Pyrite | Level 9
Thats not the point! I cannot use Proc contents inside a macro statement!
Tom
Super User Tom
Super User
Can you edit your post and move the examples into SAS code block so that formatting is preserved?
data_null__
Jade | Level 19

 

DOSUBL Function

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

Quentin
Super User

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

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
DavePrinsloo
Pyrite | Level 9
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
Quentin
Super User
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.
The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
DavePrinsloo
Pyrite | Level 9
The called macro ut_fmt2type has been replaced by code suggested by "Tom"

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 3969 views
  • 9 likes
  • 5 in conversation