how to rename all the columns at once

Reply
New Contributor
Posts: 3

how to rename all the columns at once

Hi All,

I was working on a data-set which has around 300 variables. I want to rename all the variables with an index - for instance, first variable name as var1,var2,...... var300,

I tried this but it doesn't seem to work:

data test;

input id name $ gender $ public salary;

rename id--salary=var1-var5;

datalines;

1 a M 1 10000

2 b F 0 20000

3 c M 1 10000

4 d F 0 20000

5 e M 1 10000

6 f F 0 20000

;

It throws an error ->

rename id--salary=var1-var5;

                   __

                   47

ERROR 47-185: Given form of variable list is not supported by RENAME. Statement is ignored.

Is it possible to do it this way or is there an alternate way?

Super Contributor
Posts: 644

Re: how to rename all the columns at once

I'll admit this is a bit of a kludge but a double transposition would probably work, although it might be impractical for a very large dataset (> 10K rows), assuming all your variables are all numeric (or all character).  You probably will want to transpose by id and leave that un-renamed but that's up to you.  Leave out the first drop option if your variables do not have labels.

Proc transpose data = have

                         out = pivot (drop = _LABEL_)

               ;

     Var     _ALL_ ;

     *     by id ;     /*optional*/

Run ;

Proc transpose data = pivot (rename = (_NAME_ = _LABEL_))

                         out = want (drop = _NAME_)

                         prefix = var

                         ;

     *     by id ;     /*use it if you used it first time*/

Run ;

The general idea is that the second transpose will use the prefix to construct column names because the _NAME_ column no longer exists - instead it will supply labels to the output dataset.

BTW this is untested code...

Richard

Super Contributor
Posts: 644

Re: how to rename all the columns at once

If your table is very large, but is either all numeric or all character, you can modify the above code to apply only to the first row of the table

Proc transpose data = have (obs = 1)

                         out = pivot (drop = _LABEL_)

               ;

etc.

Then use the SQL Union set operator to append the rest of the data.  Union does not check that the variable names are the same

Proc SQL ;

     create table want2 as

          select * from want

               union

          select * from have

     ;

Quit.

If you have a mix of character and numeric and you want to span them all with var1 - var300 labelling (why on earth?) then you will need to specify either lengths or formats for the renamed variables before using the sql union to append the data:

Data want2 ;

     Length var1 8

                    var2 $20

                    var3 $1

                    var4 - var5 8

                    ;

Run ;

Proc SQL ;  /*as above */

Richard

Super Contributor
Posts: 297

Re: how to rename all the columns at once

Even though the following is more keystroke intensive than you would like, it is quite resource efficient as it uses the Datasets Procedure.  Proc Datasets can make modifications to variable attributes, without reading in the entire dataset.

DATA TEST;

INPUT ID NAME $ GENDER $ PUBLIC SALARY;

DATALINES;

1 A M 1 10000

2 B F 0 20000

3 C M 1 10000

4 D F 0 20000

5 E M 1 10000

6 F F 0 20000

RUN;

%MACRO RENAME(LIB,DSN);

%LET A = %NRSTR(%MENDSmiley Wink;

PROC CONTENTS DATA=&LIB..&DSN;

TITLE 'BEFORE RENAMING';

RUN;

PROC SQL NOPRINT;

SELECT NVAR INTO :NUM_VARS

FROM DICTIONARY.TABLES

WHERE LIBNAME="&LIB" AND MEMNAME="&DSN";

SELECT DISTINCT(NAME) INTO :VAR1-:VAR%TRIM(%LEFT(&NUM_VARS))

FROM DICTIONARY.COLUMNS

WHERE LIBNAME="&LIB" AND MEMNAME="&DSN";

QUIT;

PROC DATASETS LIBRARY = &LIB;

MODIFY &DSN;

RENAME

%DO I = 1 %TO &NUM_VARS.;

&&VAR&I = VAR&I.

%END;

QUIT;

PROC CONTENTS DATA=&LIB..&DSN.;

TITLE 'AFTER RENAMING';

RUN;

%MEND RENAME;

%RENAME(WORK,TEST);

I hoe that you find this useful.

Regards,

Scott

Respected Advisor
Posts: 3,900

Re: how to rename all the columns at once

Below code should work:

data test;
  input id name $ gender $ public salary;
/*  rename id--salary=var1-var5;*/
  datalines;
1 a M 1 10000
2 b F 0 20000
3 c M 1 10000
4 d F 0 20000
5 e M 1 10000
6 f F 0 20000
;
run;

proc sql noprint;
  select cats(name, '= var', varnum) into :rename_list separated by ' '
  from dictionary.columns
  where libname='WORK' and memname='TEST'
  ;
quit;

proc datasets lib=work;
  modify test;
    rename &rename_list;
  run;
quit;


Ask a Question
Discussion stats
  • 4 replies
  • 1819 views
  • 0 likes
  • 4 in conversation