topic Re: how to rename all the columns at once in SAS Programming

how to rename all the columns at once

sasbuddy — Fri, 04 Apr 2014 04:20:02 GMT

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;

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?

Re: how to rename all the columns at once

RichardinOz — Fri, 04 Apr 2014 05:04:42 GMT

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

Re: how to rename all the columns at once

Scott_Mitchell — Fri, 04 Apr 2014 05:10:23 GMT

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(%MEND;);

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

Re: how to rename all the columns at once

RichardinOz — Fri, 04 Apr 2014 05:27:36 GMT

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

Re: how to rename all the columns at once

Patrick — Fri, 04 Apr 2014 06:32:54 GMT

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;