Renaming many variables at once from a data set list

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Renaming many variables at once from a data set list

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!

Capture.PNG


Accepted Solutions
Solution
‎03-25-2013 10:24 PM
Super Contributor
Posts: 644

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

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

View solution in original post


All Replies
Super User
Posts: 17,784

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

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;

New Contributor
Posts: 3

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

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.

PROC Star
Posts: 7,360

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

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;

Solution
‎03-25-2013 10:24 PM
Super Contributor
Posts: 644

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

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

New Contributor
Posts: 3

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

Thanks, Richard. This did exactly what I needed.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 2558 views
  • 0 likes
  • 4 in conversation