topic Re: How to rename all the column names of a dataset in SAS Programming

How to rename all the column names of a dataset

SHAWNCHACKO — Fri, 02 Nov 2018 00:02:26 GMT

Hi Everyone,

I am trying to rename all my column names to something that is meaningful to the audience.

My column names vary from demand1-demand288. I would like it to be renamed to

Input Table:

Demand1, Demand2, Demand3, Demand4........Demand288

Output Table:

Demand0000, Demand0005,Demand0010.........Demand2355

Required:
Demand1 --> Demand0000

Demand2 --> Demand0005
Demand3 --> Demand0010
:

Demand12 --> Demand0055
Demand13 --> Demand1000
:

Demand288 --> Demand2355

Sample Code:

DATA WORK.DEMAND_TEST;;

ARRAY DEMAND [288] DEMAND1 - DEMAND288;

run;

%MACRO TEST_RENAME;

DATA WORK.RENAME_TEST;

SET WORK.DEMAND_TEST;

%DO i = 0 %TO 23 %BY 1;

%DO j = 0 %TO 55 %BY 5;

k+1;

CALL SYMPUTX("k" ,k);

RENAME DEMAND&k = DEMAND&i&j;

OUTPUT;

%END;

RUN;

%MEND;

%TEST_RENAME;

Any help is much appreciated.

Re: How to rename all the column names of a dataset

Reeza — Fri, 02 Nov 2018 00:11:08 GMT

@SHAWNCHACKO wrote:

Hi Everyone,

I am trying to rename all my column names to something that is meaningful to the audience.

My column names vary from demand1-demand288. I would like it to be renamed to

Input Table:

Demand1, Demand2, Demand3, Demand4........Demand288

Output Table:

Demand0000, Demand0005,Demand0010.........Demand2355

Required:
Demand1 --> Demand0000

Demand2 --> Demand0005
Demand3 --> Demand0010
:

:

Demand12 --> Demand0055
Demand13 --> Demand1000
:

:

Demand288 --> Demand2355

Sample Code:

DATA WORK.DEMAND_TEST;;

ARRAY DEMAND [288] DEMAND1 - DEMAND288;

run;

%MACRO TEST_RENAME;

DATA WORK.RENAME_TEST;

SET WORK.DEMAND_TEST;

%DO i = 0 %TO 23 %BY 1;

%DO j = 0 %TO 55 %BY 5;

k+1;

CALL SYMPUTX("k" ,k);

RENAME DEMAND&k = DEMAND&i&j;

OUTPUT;

%END;

%END;

RUN;

%MEND;

%TEST_RENAME;

Any help is much appreciated.

Use the approach in my answer here

https://communities.sas.com/t5/SAS-Programming/How-to-rename-a-variable-in-a-dataset-with-value-of-another/td-p/418788

You can also use the fact that your new index is essentially (varnum-1)*5

Re: How to rename all the column names of a dataset

Kurt_Bremser — Fri, 02 Nov 2018 09:01:08 GMT

Don't rename, transpose and transform:

proc transpose
  data=have
  out=trans (rename=(col1=demand))
;
var demand:;
run;

data want;
set trans;
*where demand ne .;
length demand_type $4;
demand_type = put(input(substr(_name_,7),best.),z4.);
drop _name_;
run;

Your want dataset will be much easier to work with, and if you uncomment the where, most probably much smaller.

Re: How to rename all the column names of a dataset

SHAWNCHACKO — Wed, 07 Nov 2018 02:25:57 GMT

Hi All,

This is what I figured out. I will look at all the other suggested solutions by users.

%MACRO TEST_RENAME;
	DATA WORK.RENAME_TEST;
	SET WORK.DEMAND_TEST;
	%let k=0;
		%DO i = 0 %TO 23 %BY 1;
			%DO j = 0 %TO 55 %BY 5;
				%let i=%sysfunc(putn(&i,Z2));
				%let j=%sysfunc(putn(&j,Z2));
			  	%let k=%eval(&k+1);

				RENAME DEMAND&K = DEMAND&i&j;
			%END;
		%END;
	RUN;
%MEND;
%TEST_RENAME;