SAS macro to change column names

Question

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_"

Do you want to change all table column names so that the character columns are given a "XC_" prefix and the numeric columns are given a "XN_" prefix? If you want something different then please edit your original question to make it clearer. If this is what you want then can you explain what you want to achieve once the columns are renamed (in case there is a simpler way to achieve what you want.) — Amir, Sep 06 '18 at 08:58
@Amir except the column names that specified in the macro variable should not be changed — LeeKing, Sep 06 '18 at 09:59
What have you tried ? Do you know how to use `Proc DATASETS` to rename variables in an existing data set ? Do you know about `Proc CONTENTS` or `Proc SQL`s `DICTIONARY.COLUMNS` ? Did you swap actual desired prefixes when you stated X**C** for **N**umeric and X**N** for **C**haracter ? — Richard, Sep 06 '18 at 10:31

score 3 · Answer 1 · answered Sep 06 '18 at 11:11

A proper utility macro will accept all the processing abstractions as parameters:

data=, the data set to operate on
copy=, the variables to leave alone (copy is an homage to the copy statement in TRANSPOSE)
char_prefix=XC_, prefix to apply to names of not-copied character variables, default is XC_
num_prefix=XN_, prefix to apply to names of not-copied numeric variables, default is XN_

The innards of a utility macro is a black box. Sometimes the design of the innards are specified to allow DATA and PROC steps to occur.

Sample code

Proc SQL is used to fill a macro variable with a list of old=new name pairs that can be used in a RENAME statement executed by Proc DATASETS

%macro Fixer ( data=, copy=, char_prefix=XC_, num_prefix=XN_ );

  %let syslast = &data;

  %local lib mem rename_clause;

  %let lib = %scan(&syslast,1);
  %let mem = %scan(&syslast,2);

  proc sql noprint;
    select 
      trim(name) || '=' || 
      case type
        when 'num'  then "&num_prefix" || name
        when 'char' then "&char_prefix" || name
        else ''
      end
      into :rename_clause separated by ' '
    from 
      dictionary.columns
    where 
          libname = "&lib"
      and memname = "&mem"
      and indexw (%upcase("&copy"), upcase(name)) = 0
  ;

  proc datasets nolist;
    modify &data;
    rename &rename_clause;
  run;
  quit;
%mend;


data class;
  set sashelp.class;
  teacher = 'Davidowski';
run;

options mprint;

%Fixer ( data=class, copy=Name )

Other times the innards must not generate any code. For this question such a macro would use macro function %SYSFUNC to access data set functions such as open, close, attrn, vartype, varname as it tests renaming criteria and accumulates old=new name pairs that will be emitted for use by the callee.

SAS macro to change column names

1 Answers1