topic Re: Renaming many variables at once from a data set list in SAS Programming

Renaming many variables at once from a data set list

zachary_oliver — Mon, 25 Mar 2013 15:11:22 GMT

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!

Re: Renaming many variables at once from a data set list

Reeza — Mon, 25 Mar 2013 15:18:24 GMT

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;

Re: Renaming many variables at once from a data set list

zachary_oliver — Mon, 25 Mar 2013 15:23:35 GMT

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.

Re: Renaming many variables at once from a data set list

art297 — Mon, 25 Mar 2013 15:33:54 GMT

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;

Re: Renaming many variables at once from a data set list

RichardinOz — Tue, 26 Mar 2013 02:24:49 GMT

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

Re: Renaming many variables at once from a data set list

zachary_oliver — Tue, 26 Mar 2013 13:27:39 GMT

Thanks, Richard. This did exactly what I needed.