BookmarkSubscribeRSS Feed
statadm
Fluorite | Level 6
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.
10 REPLIES 10
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Obsidian | Level 7
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
Lapis Lazuli | Level 10
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
Fluorite | Level 6
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
Lapis Lazuli | Level 10
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
Fluorite | Level 6
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
Opal | Level 21
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
Super User
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
Fluorite | Level 6
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
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 4066 views
  • 0 likes
  • 6 in conversation