Renaming many variables at once from a data set list

[zachary_oliver]

Hi, I am attempting to rename 417 columns labeled COL1-COL417 using names from a separate data set. (see the data set below)

I have a separate data set with one column that has all 417 names that I would like to use to replace COL1-COL417. What is an efficient way to approach this?

Thank you!

[Reeza]

Depends how automated you want it.

One quick way, is in the excel sheet create a concatenate formula in the third column that creates the field as follows:

=concatenate(A1, " = ", B1)

Col1=New_Name1

Then copy and paste that into a code window as follows:

Data rename;

set have;

rename

*Paste the excel formula output here;

;

run;

[zachary_oliver]

Thanks for the quick response.

I would like to automate this process because it is something that I will be running for data sets with varying numbers of columns. Therefore, taking myself out of the program would be ideal.

[art297]

If I understand what you are trying to do, you might be able to use something like the following:

/*create some test data*/

data have;

  input iocode $ col1 col2 col3;

  cards;

1111A0 1 2 3

1111B0 4 5 6

111200 7 8 9

;

/*add record number to the data*/

data need;

  set have;

  recnum=_n_;

run;

proc sql noprint;

  select "col"||strip(put(recnum,3.))||"=_"||strip(iocode)

    into :renames separated by " "

      from need

  ;

quit;

data want;

  set have (rename=(&renames.));

run;

[RichardinOz · Accepted solution]

You might take advantage of the SQL UNION operator which will append a table by column position rather than name.

Base SAS(R) 9.2 Procedures Guide

/* get column names */

Proc SQL NoPrint ;

     Select names

     Into :colnames

     Separated by ' '

     From namelist

     ;

Quit ;

/* create empty table */

Data temp ;

     Length Industry_IO_Code $ 8

               &colnames 8

               ;

Run ;

/* append data */

Proc SQL ;

   Create table want as

     Select * from temp (obs = 0)

          Union

     Select * from have

     ;

Quit ;

(Untested code)

Richard

Message was edited by: Richard Carson Apologies: Forgot the From clause in the first SQL

[zachary_oliver]

Thanks, Richard. This did exactly what I needed.