topic Re: SAS macro to change column names in SAS Procedures

SAS macro to change column names

Geo- — Thu, 06 Sep 2018 06:35:07 GMT

Anyone know how to use macro to change names of a table column ,except the ones that you point out.numeric type columns are added prefix "XC_" and char type columns are added prefix "XN_"

Re: SAS macro to change column names

andreas_lds — Thu, 06 Sep 2018 06:52:05 GMT

This has been asked and answered so many times ... you will find adaptable code, if you try.

Re: SAS macro to change column names

Kurt_Bremser — Thu, 06 Sep 2018 07:54:34 GMT

First, acquaint yourself with the DATASETS procedure, as that will do the work.

Then retrieve the current column names in a data _null_ step from sashelp.vcolumn, and use call execute to dynamically create the code for the datasets procedure.

data _null_;
set sashelp.vcolumn (where=(libname="LIBRARY" and memname = "DATASET")) end=eof;
length prefix $3;
if _n_ = 1 then call execute("
  proc datasets library=LIBRARY nolist;
  modify DATASET;
  rename
");
if type = 'char'
then prefix = "XC_";
else prefix = "XN_";
call execute(trim(name !! '=' !! prefix !! trim(name) !! ' '));
if eof then call execute("
  ;
  quit;
");
run;

As you can see, no macro needed.

Note that I use double quotes wherever the use of macro variables will come in handy (libname, memname, prefixes).

If you replace those items with macro variables, you can wrap the code into a macro definition for easier re-use.

Re: SAS macro to change column names

RW9 — Thu, 06 Sep 2018 08:19:21 GMT

Please give a definative example, with test data in the form of a datastep, and what the output should look like. Renaming variables is as simple as:

data want;
  set have (keep=xc_1 xc_2 xc_3);  /* Just to show an example */
  rename xc_1-xc-3=xn_1-xn_3;
run;

Or you can normalise, then transpose.

Or you can have arrays.

All ways of using plain Base SAS code to achieve the end result.

Re: SAS macro to change column names

s_lassen — Thu, 06 Sep 2018 09:21:29 GMT

I would use SQL to put the renames into macro variables, like this:

proc sql noprint;
  select cats(name,'=',case when type='num' then 'XN_' else 'XC_' end,name) into :renames separated by ' '
  from dictionary.columns where libname='SASHELP' and memname='CLASS';
quit;
%put &renames;
data test;
  set sashelp.class;
  rename &renames;
run;

If you have some ID columns that you do not want renamed, just add "and name not in(<variables>)" in the WHERE clause.

E.g.:

proc sql noprint;
  select cats(name,'=',case when type='num' then 'XN_' else 'XC_' end,name) into :renames separated by ' '
  from dictionary.columns where libname='SASHELP' and memname='CLASS' and name not in('NAME','AGE');