data set column names into macro variable(s)

问题:

What is the simplest way to put all column names of a given dataset into a macro variable?

Another question, how should I put names of all "numeric" columns into one macro variable and names of all character columns into another macro variable?

回答1:

Depending on what you're doing you can also reference all character variables with _character_ and numerical variables with _numeric_.

*doesn't do anything but illustrates how to reference all variables of a specific type;
data want;
   set sashelp.class;
   array _c1(*) _character_;
   array _n1(*) _numeric_;

run;

回答2:

If you need to do this for a number of tables then you could try defining a macro for that purpose, e.g.:

%macro get_cols(lib,mem,mvar,type);
   %global &mvar;

   proc sql noprint;
      select
         name
      into
         :&mvar separated by ' '
      from
         dictionary.columns
      where
             libname eq upcase("&lib")
         and memname eq upcase("&mem")

         %if %upcase(&type) ne ALL %then
         and upcase(type) eq upcase("&type");

      ;
   quit;

   %put &mvar = &&&mvar;
%mend get_cols;

Then you could call it, as required:

/* character variables */
%get_cols(sashelp,class,cvars,char);

/* numeric variables */
%get_cols(sashelp,class,nvars,num);

/* all variables */
%get_cols(sashelp,class,vars,all);

回答3:

using proc sql into clause with dictionary.columns is one of easiest way to make macro variables for your purpose. Below query uses sashelp.class, you can use your table name and libname instead

/* for all variables*/
Proc sql noprint;
select name into :macvar1 separated by ',' from
dictionary.columns
where upcase(memname) = 'CLASS'
and upcase(libname)  = 'SASHELP';

/* for numeric variables*/
 Proc sql noprint;
 select name into :macvar2 separated by ',' from
 dictionary.columns
 where upcase(memname) = 'CLASS'
 and upcase(libname)  = 'SASHELP'
 and upcase(type) = 'NUM';


  /* for character variables*/
  Proc sql noprint;
 select name into :macvar3 separated by ',' from
 dictionary.columns
 where upcase(memname) = 'CLASS'
and upcase(libname)  = 'SASHELP'
and upcase(type) = 'CHAR';

%put value of all variables is &macvar1;
%put value of numeric variables is &macvar2;
%put value of character variables is &macvar3;