Help with renaming large groups of variables

[statadm]

I'm sure there is an easy solution, but I can't seem to figure it out.

I just need to rename a large group of variables that contain the prefix E4 to E1.

E4CONT
E4_AGE
E4_DATE

rename to:

E1CONT
E1_AGE
E1_DATE

I have to do this three times where E4 should be E1, E5 should be E2, and E6 should be E3.

Thanks in advance for your help. I've been struggling with this for awhile.

[SPR]

Hello Statadm,

This is a possible solution:
[pre]
data i;
E4CONT=1;
E4_AGE=2;
E4_DATE=3;
run;
%macro a (in=E4, out=E1);
proc SQL;
select COUNT(distinct name) as n into :n
from sashelp.vcolumn
where libname="WORK" and memname="I" and SUBSTR(UPCASE(name),1,2)="&in";
%let n=%TRIM(&n);
select distinct name as names into:n1-:n&n
from sashelp.vcolumn
where libname="WORK" and memname="I" and SUBSTR(UPCASE(name),1,2)="&in";
quit;
data i;
set i;
rename
%do i=1 %to 3;
&&n&i=&out.%SUBSTR(&&n&i,3)
%end;
;
run;
%mend a;
%a(in=E1, out=E5);
[/pre]
Sincerely,
SPR

[chang_y_chung_hotmail_com]

I am not sure if it is a good idea to rewrite the whole dataset just to rename the variables... I would rather recommend using proc datasets. Something like this sas-l posting.

[BruceBrad]

Here is another approach. This macro removes the first character from almost all the variable names in a file. You can easily modify to do what you want.

%macro HILDARenameStripFirst(dataset);
/* Generates a rename statement of the form
Xvar1=var1 Xvar2=var2 etc
where XvarN includes all variables (except Xwaveid).
Usage example :

data long2;
set hilda.rperson_a (rename=(%HILDARenameStripFirst(hilda.rperson_a)) );
run;

BB. Aug07. I used SAS paper 107-28 by Derek Morgan as inspiration.
-------------------------------------------------------------------- */
%let DS = %sysfunc(open(&dataset,i)); /* open dataset to get variable names */
%if (&DS = 0) %then %put %sysfunc(sysmsg()); /* if cant open */
%else
%do i=1 %to %sysfunc(attrn(&DS,NVARS));
%let varname = %sysfunc(varname(&DS,&i)); /* get the ith variable name */
%let newvarname = %substr(&varname,2); /* strip off the first character */
%if %upcase(&varname)^=XWAVEID %then %do;
%* output code here;
&varname=&newvarname
%end;
%end;
%let CloseCode = %sysfunc(close(&DS));
%mend HILDARenameStripFirst;

[statadm]

Are you suggesting I strip off the first two characters and then add back on the prefix that I want?

That should be doable.

Thanks,
Kim Message was edited by: statadm

[BruceBrad]

I was suggesting changing the following line
%let newvarname = %substr(&varname,2);
to do whatever it is you want to do. Something like the following, if I understand your requirement correctly (untested)
%let newvarname = %substr(&varname,1,1) %eval(%substr(&varname,2,1) +5) %substr(&varname,2);

[statadm]

The only problem with using this code is that once I remove the prefix, there will be a lot of variables with the same name. The prefix is what makes the variables different.

Is there any way to just change the number in the prefix for all variables to a new number?

I use macros to change variables names all the time, but only know how to do this by listing all the variables within a set range. I have too many variables to do this.

Thanks!

[art297]

I wouldn't use a macro, possibly just a macro variable created via proc sql.

Use proc sql to create a macro variable that contains ALL of the recodes you want to accomplish and then submit the macro variable, as code, within proc datasets like Chang suggested.

Should be quite easy to do.

Art

[Ksharp]

If you have lots and lots of variables.


[pre]
data have;
E4CONT=1;
E4_AGE=2;
E4_DATE=3;
run;
options mprint mlogic symbolgen;
%macro rename;
proc sql ;
select cats( name,'=',tranwrd(name,'E4','E1'))
from dictionary.columns where libname='WORK' and memname='HAVE';

select cats( name,'=',tranwrd(name,'E4','E1'))
into : rcode1 - : rcode&sqlobs.
from dictionary.columns where libname='WORK' and memname='HAVE';
quit ;
proc datasets library=work ;
modify have ;
rename %do i=1 %to &sqlobs.;
&&rcode&i
%end;
;
quit ;
%mend ;

%rename
[/pre]


Ksharp

[statadm]

I had trouble getting any of the examples to work, so I took the easy route and did it the amateurish way just so I could get it done. I thought I would share.

Thank you to everyone for your help. Sorry for the all caps, I have to program in all CAPS here.
***************************************************************;

PROC TRANSPOSE DATA=E2 OUT=ONE;
VAR _ALL_;
RUN;

DATA TWO ; SET ONE;
_NAME_ = TRANWRD(_NAME_,'E4','E1');
_NAME_ = TRANWRD(_NAME_,'E5','E2');
_NAME_ = TRANWRD(_NAME_,'E6','E3');

DATA THREE; SET TWO;
RENAME _NAME_ = NEW;

PROC TRANSPOSE DATA=THREE OUT=FOUR;
ID NEW;
IDLABEL _LABEL_;
VAR COL1 COL2;

PROC PRINT;
RUN;

Message was edited by: statadm Message was edited by: statadm

[Ksharp]

Or If you like pure dataset code


[pre]
data i;
E4CONT=1;
E4_AGE=2;
E4_DATE=3;
E5CONT=1;
E5_AGE=2;
E5_DATE=3;
E6CONT=1;
E6_AGE=2;
E6_DATE=3;
run;
data _null_;
set sashelp.vcolumn(where=(libname='WORK' and memname='I')) end=last;
if _n_ = 1 then call execute('proc datasets library=work nolist;modify i;rename ');
call execute(strip(name)||'='||translate(name,'123','456'));
if last then call execute(';quit;');
run;
[/pre]

Ksharp