Rename column names in reversed order for suffix in the column names

[Suzy_Cat]

Hi there,

Can anyone help me out to change the column names with suffix by  digit number in ascending order to descending order?

data have;

input a_1 $ b_2 $ c_3 $ d_4 e_5;

cards;

a . a 1 3

. b . 2 4

a a a . 5

. . b 3 5

a a a . 6

a a a . 7

a a a 2 8

;

run;

data want;

input a_5 $ b_4 $ c_3 $ d_2 e_1;

cards;

a . a 1 3

. b . 2 4

a a a . 5

. . b 3 5

a a a . 6

a a a . 7

a a a 2 8

;

run;

Thanks in advance 🙂

Suzy

[Tom · Accepted solution]

Much easier if they actually share the same prefix before the numeric suffix.

data want;
  set have;
  rename x1-x5=x5-x1;
run;

[Suzy_Cat]

Cool . Nice and easy. Thank you Tom!

[mkeintz]

Essentially you want to use PROC DATASETS to do a rename of the variables in your dataset.   You can use the DICTIONAIRES capability of PROC SQL to fetch metadata (number of variables,  variable names, and variable position) to construct a rename expression to use in PROC DATASETS.  Something like this will work:

data have;
  input a_1 $ b_2 $ c_3 $ d_4 e_5;
cards;
a . a 1 3
. b . 2 4
a a a . 5
. . b 3 5
a a a . 6
a a a . 7
a a a 2 8
;

proc sql noprint;
  select cats(nvar) into :nv from dictionary.tables   
    where libname='WORK' and memname='HAVE' ;
  %put &=nv;

  select cats(name,'=',scan(name,1,'_'),'_',&nv+1-varnum) 
    into :rename_list separated by ' ' from dictionary.columns
    where libname='WORK' and memname='HAVE';
  %put =&rename_list;
quit;

proc datasets library=work  nolist ;
  modify have;
  rename &rename_list ;
quit;

The proc sql first gets the number of variables into macrovar NV.  Then it gets all the variable names (in storage order), appends and equal sign,  and then append just the first part of the variable name, and an underscore, and finaly a number calculated as &NV+1-varnum, where varnum is the storage position.

This produces the rename statement in PRFOC DATASETS, as below:

    rename a_1=a_5   b_2=b_4  c_3=c_3  d_4=d_2  e_5=e_1;

But even though it works, it also generates the error message below:

841  proc datasets library=work  nolist ;
842    modify have;
843    rename &rename_list ;
NOTE: Renaming variable a_1 to a_5.
NOTE: Renaming variable b_2 to b_4.
ERROR: Variable c_3 already exists on file WORK.HAVE.
NOTE: Renaming variable d_4 to d_2.
NOTE: Renaming variable e_5 to e_1.
844  quit;

You can eliminate that by taking out the "c_3=c_3" component of the rename macrovar, by adding the where condition in proc SQL to include

  "and scan(name,-1,'_') ^= cats(&nv+1-varnum)"

proc sql noprint;
  select cats(nvar) into :nv from dictionary.tables   
    where libname='WORK' and memname='HAVE' ;
  %put &=nv;

  select cats(name,'=',scan(name,1,'_'),'_',&nv+1-varnum) 
    into :rename_list separated by ' ' from dictionary.columns
    where libname='WORK' and memname='HAVE'
    and scan(name,-1,'_') ^= cats(&nv+1-varnum);
  %put &=rename_list;
quit;

proc datasets library=work  nolist ;
  modify have;
  rename &rename_list ;
quit;

This takes advantage of some "magic" in the CATS (concatenate and strip trailing/leading blanks), because it accepts not only lists of character values ('=' and '_') and variables (i.e. the variable name), but also numeric variables (varnum)  and numeric expressions (&nv+1-varnum).