How to rename a group of variables utilize array and looping method

[stataq]

Hello,

I have a dataset 'Have' and I would like to rename the variables that start with 'COL'  to COL1-COLx, where x is how many variables that were using name COL in data 'Have'.  In my sample data, I would like to change vars COL1 COL3 COL88 to COL1 COL2 COL3. The 'Want' outcome is the bottom one:

What should I do? Thinking of getting all variable that start with COL into array and then do the loop for rename, but get lost during the process. Could anyone guide me on this? thanks.

data have;
input group $  COL1 COL3 COL88 value $;
datalines;
g1 30 45 36 Y
g2 37 25 55 N
g2 76  88 99 N
g3 22 74 . Y
g3 84 . 25 Y
g4 15  88 99 N
g4 21	 78 65 Y
g4 94 . . N
g4 73 8 78 Y
;
run;

[ballardw]

First define in quite a bit more detail what "rename the variables that start with 'COL' to COL1-COLx, where x is how many variables that were using name COL in data 'Have'. " means. Walk through example(s).

Best is to also include an input data set and what the output data set is expected to look like for that input as I am not at all sure how you get to to your "want" result.

Note: Renaming is not done on an observation by observation basis which is what an "array" would do.

If this is the result of a Proc Transpose (which is a common source of Col1-ColN naming) it might be better to go back one step and see if an ID statement might not help rename the data as desired. Which would mean provide an example of data input to the Proc Transpose and the transpose code.

[Tom]

You cannot use an ARRAY to RENAME variables.  You could use arrays to COPY the values to NEW variables.

If you know how many variables there are then the code to do that could look like this:

%let ncol=3;
data want;
  set have;
  array old col: ;
  array new [&ncol] ;
  do _n_=1 to dim(old);
    new[_n_]=old[_n_];
  end;
  drop col: ;
  rename new1-new&ncol=col1-col&ncol;
run;

But if you want to just RENAME the variables (which will take less time and resources) you will need to do some CODE GENERATION instead.  One way would be to use list of variable names to generate a series of OLD=NEW rename pairs that could be used with a RENAME statement or a RENAME= dataset option.

One easy way is to make an empty dataset that only has the COL... variables so that you can use the VARNUM metadata item to generate the new names.

data step1;
  set have(obs=0);
  keep col: ;
run;

proc sql noprint;
select catx('=',nliteral(name),cats('COL',varnum))
  into :renames separated by ' '
  from dictionary.columns
  where libname='WORK' and memname='STEP1'
;
quit;

You can now use this to either make a NEW dataset.

data want;
  set have;
  rename &renames ;
run;

Or to modify the old one

proc datasets nolist lib=work;
  modify have;
    rename &renames;
  run;
quit;

[Tom]

If you want to get cute you can generate the RENAMES macro variable directly from the HAVE dataset by using an ARRAY and various SAS functions.

data _null_;
  if 0 then set have;
  array old col: ;
  call symputx('renames',' ');
  do i=1 to dim(old);
    call symputx('renames',catx(' ',symget('renames')
                          ,catx('=',nliteral(vname(old[i])),cats('COL',i))
    ));
  end;
  stop;
run;

[JOL · Accepted solution]

data test;
input group $ COL1 COL3 COL88 value $;
datalines;
g1 30 45 36 Y
g2 37 25 55 N
g2 76 88 99 N
g3 22 74 . Y
g3 84 . 25 Y
g4 15 88 99 N
g4 21 78 65 Y
g4 94 . . N
g4 73 8 78 Y
;
run;

data test;
set test;
array new[*] COL:; /* This Array references the original columns */
array CL[3]; /* This Array will create the new columns needed
Use a different name from the original
You must put a number of array elements*/
do x = 1 to dim(new);
CL[x] = new[x];
end;

keep group CL: value;
run;