topic Help with renaming large groups of variables in SAS Programming

Help with renaming large groups of variables

statadm — Wed, 04 May 2011 20:54:54 GMT

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.

Re: Help with renaming large groups of variables

SPR — Wed, 04 May 2011 21:33:35 GMT

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

Re: Help with renaming large groups of variables

chang_y_chung_hotmail_com — Wed, 04 May 2011 21:51:23 GMT

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.

Re: Help with renaming large groups of variables

BruceBrad — Thu, 05 May 2011 00:32:53 GMT

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;

Re: Help with renaming large groups of variables

statadm — Thu, 05 May 2011 13:03:06 GMT

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

Re: Help with renaming large groups of variables

statadm — Thu, 05 May 2011 19:19:31 GMT

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!

Re: Help with renaming large groups of variables

art297 — Thu, 05 May 2011 22:45:32 GMT

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

Re: Help with renaming large groups of variables

BruceBrad — Fri, 06 May 2011 02:17:35 GMT

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);

Re: Help with renaming large groups of variables

Ksharp — Fri, 06 May 2011 07:02:36 GMT

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

Re: Help with renaming large groups of variables

statadm — Fri, 06 May 2011 16:56:06 GMT

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

Re: Help with renaming large groups of variables

Ksharp — Mon, 09 May 2011 02:24:47 GMT

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