SAS - renaming variables

Question

I am trying to change the names of variables in my table/dataset. I went through several websites and this discussion forum, but I didn´t manage to find any code that would work properly in my case (i am a newcomer to SAS).

My dataset contains 103 columns and I would like to rename the first 100 columns. The name of the first column is CFT(1), CFT(2) of the second column,..., CFT(100) of the 100th column. New variables can be called for example CFT_n(1),...,CFT_n(100).

The code I was using is following:

data vystup_m200_b;
set vystup_m200_a;
rename 'cft(1)'n - 'cft(100)'n='cft(1)_n'n - 'cft(100)_n'n;
run;   

But I obtain an error stating:

Aplhabetic prefixes for enumerated variables (cft(1)-cft(100)) are different.

Thank you for any suggestion what I am doing wrong.

Answer 1

If your variables are sequentially named, a simple macro will suffice:

option validvarname = any;

data ren;
   array _a[*] 'cft(1)'n 'cft(2)'n 'cft(3)'n ( 1 2 3);
   do i = 1 to 10;
      output;
      end;
   drop i;
run;


%macro rename_loop;
    %local i;
    %do i = 1 %to 3;
         "cft(&i)"n = "cft(&i)_n"n
    %end;
%mend rename_loop;

proc datasets lib = work nolist nowarn nodetails;
    modify ren;
    rename %rename_loop;
    run;
quit;

This should work more or less instantaneously, regardless of the size of the dataset, as it only needs to update the metadata.

Answer 2

Another solution, which is renaming variables after upload:

proc import datafile="\\folder\RUN_00.xlsx"
dbms=xlsx out=run_00 replace;
run;

data rename;
length ren $32767;
set run_00(obs= 1);
keep ren delka;
array cfte{*} CFT:;
do i=1 to dim(cfte);
ren=strip(ren)||" 'cft("||strip(i)||")'n='cft_"||strip(i)||"_00'n";
delka=length(ren);
end;
call symputx("renam",ren);
run;

proc datasets library=work;
modify run_00;
rename &renam;
run;

Answer 3

Renaming is fastest. I would look to a more general solution that doesn't require knowing anything like the name or how many or if you need name literals.

data ren;
   array _a[*] 'cft(1)'n 'cft(2)'n 'cft(3)'n (1 2 3);
   do i = 1 to 10;
      output;
      end;
   drop i;
   run;
proc print;
   run;
proc transpose data=ren(obs=0) out=ren2;
   run;
proc sql noprint;
   select catx('=',nliteral(_name_),nliteral(cats(_name_,'_n'))) 
      into :renamelist separated by ' '
   from ren2;
   quit;
   run;
%put NOTE: &=renamelist;
proc datasets nolist;
   modify ren;
   rename &renamelist;
   run;
   contents data=ren varnum short;
   quit;

Answer 4

Even with validvarname=any the numeric suffix on a numbered variable list have to have the number as the last part of the name. You "could" use the features of PROC TRANSPOSE to flip-flop the data to rename the variables. This is only advisable if the data are rather small.

data ren;
   array _a[*] 'cft(1)'n 'cft(2)'n 'cft(3)'n ( 1 2 3);
   do i = 1 to 10;
      output;
      end;
   drop i;
   run;
proc transpose data=ren out=ren2;
   run;
proc transpose data=ren2 out=renamed(drop=_name_) suffix=_N;
   id _name_;
   run;