topic Re: rename multiple variables with a series of numbers in SAS Programming

rename multiple variables with a series of numbers

Saba1 — Fri, 13 Dec 2019 04:22:55 GMT

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;

Re: rename multiple variables with a series of numbers

japelin — Fri, 13 Dec 2019 04:58:48 GMT

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;

Re: rename multiple variables with a series of numbers

japelin — Fri, 13 Dec 2019 06:56:06 GMT

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

Re: rename multiple variables with a series of numbers

andreas_lds — Fri, 13 Dec 2019 07:30:33 GMT

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;

Re: rename multiple variables with a series of numbers

gamotte — Fri, 13 Dec 2019 10:59:05 GMT

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;

Re: rename multiple variables with a series of numbers

Saba1 — Mon, 16 Dec 2019 06:22:03 GMT

Thank you. It is really helpful.