Hi
I have a dataset with dummy variables created for different IDs and Time (using proc glmmod). I have more than 1000 IDs. I want to rename all the columns of ID dummy variables from "ID_10001 ID_10002 ID_10006 ID_10010 ID_11114
" to "ID1 ID2 ID3 ID4 ID5
" and so on.
Looking for a code that can help me achieving this purpose.
data have;
input Y X1 X2 ID_10001 ID_10002 ID_10006 ID_10010 ID_11114 Year_2005 Year_2006 Year_2007 ;
datalines;
1010 100 0 1 0 0 0 0 1 0 0
1022 250 0 1 0 0 0 0 0 0 1
1134 180 0 0 1 0 0 0 0 0 1
1014 450 1 0 0 1 0 0 1 0 0
2526 450 1 0 0 1 0 0 0 1 0
1014 300 0 0 0 1 0 0 0 0 1
4012 999 0 0 0 0 1 0 1 0 0
3179 850 1 0 0 0 1 0 0 1 0
1012 720 0 0 0 0 1 0 0 0 1
3175 800 0 0 0 0 0 1 1 0 0
;
run;
I modified to rename multiple variables.
Check below:
%Macro Mrename(ds,prefix);
data _null_;
call symputx('vcnt',count("&prefix",' ')+1);
run;
%do i=1 %to &vcnt;
%let prefix&i=%scan(&prefix,&i,' ');
%end;
data _null_;
dsid=open("&ds.",'I');
array varcnt{&vcnt};
if dsid>0 then do;
vars=attrn(dsid,'nvars');
do i=1 to vars;
%do k=1 %to &vcnt;
if find(upcase(varname(dsid,i)),upcase("&&prefix&k"))=1 then do;
j+1;
varcnt{&k}+1;
call symputx(cats('vnm_old',j),varname(dsid,i));
call symputx(cats('vnm_new',j),cats("&&prefix&k",varcnt{&k}));
end;
%end;
end;
call symputx('renamevars',j);
rc=close(dsid);
end;
run;
data &ds._renamed;
set &ds.;
%do i=1 %to &renamevars.;
rename &&vnm_old&i. = &&vnm_new&i.;
%end;
run;
%Mend Mrename;
/* Specify words to rename separated by spaces. */
%Mrename(work.have,ID YEAR);
/* You can control prefix to uppercase or lowercase. */
/* Both are the same... =) */
/*%Mrename(work.have,id year);*/
Hi, Saba1
try this code
data _null_;
dsid=open('work.have','I');
if dsid>0 then do;
vars=attrn(dsid,'nvars');
do i=1 to vars;
if find(varname(dsid,i),'ID')=1 then do;
j+1;
call symputx(cats('vnm_old',j),varname(dsid,i));
call symputx(cats('vnm_new',j),cats('ID',j));
end;
end;
call symputx('renamevars',j);
rc=close(dsid);
end;
run;
%macro Mrename;
data renamed;
set have;
%do i=1 %to &renamevars.;
rename &&vnm_old&i. = &&vnm_new&i.;
%end;
run;
%Mend Mrename;
%Mrename;
I modified to rename multiple variables.
Check below:
%Macro Mrename(ds,prefix);
data _null_;
call symputx('vcnt',count("&prefix",' ')+1);
run;
%do i=1 %to &vcnt;
%let prefix&i=%scan(&prefix,&i,' ');
%end;
data _null_;
dsid=open("&ds.",'I');
array varcnt{&vcnt};
if dsid>0 then do;
vars=attrn(dsid,'nvars');
do i=1 to vars;
%do k=1 %to &vcnt;
if find(upcase(varname(dsid,i)),upcase("&&prefix&k"))=1 then do;
j+1;
varcnt{&k}+1;
call symputx(cats('vnm_old',j),varname(dsid,i));
call symputx(cats('vnm_new',j),cats("&&prefix&k",varcnt{&k}));
end;
%end;
end;
call symputx('renamevars',j);
rc=close(dsid);
end;
run;
data &ds._renamed;
set &ds.;
%do i=1 %to &renamevars.;
rename &&vnm_old&i. = &&vnm_new&i.;
%end;
run;
%Mend Mrename;
/* Specify words to rename separated by spaces. */
%Mrename(work.have,ID YEAR);
/* You can control prefix to uppercase or lowercase. */
/* Both are the same... =) */
/*%Mrename(work.have,id year);*/
Can be solved by using sashelp.vcolumn and call execute:
data _null_;
set sashelp.vcolumn(where=(libname = 'WORK' and memname = 'HAVE' and name like 'ID%')) end=jobDone;
if _n_ = 1 then do;
call execute('proc datasets library=work nolist; modify have; rename ');
end;
number + 1;
call execute(cats(Name, '=', cats('ID', number)));
if jobDone then do;
call execute(';quit;');
end;
run;
Hello,
data _NULL_;
set have;
length _NAME_ $32;
call execute('data want; set have; rename');
do while(_NAME_ ne "_NAME_");
call vnext(_NAME_);
if substr(_NAME_,1,3)="ID_" then do;
i+1;
call execute(cats(_NAME_,'=ID_',i));
end;
end;
call execute('; run;');
stop;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.