How to change RwCENREG to REGIONw?

[nwang5]

The original variables include R9CENREG R10CENREG  R11CENREG

I want to change them as:

region9=R9CENREG ;
region10=R10CENREG ;
region11=R11CENREG ;

I was wondering if there is any easier way

[Reeza]

• proc sql noprint;
  select catt (name, '=', catt('Region', compress(name, , 'kd'))) into :rename_list separated by " "
  from sashelp.vcolumn
  where libname='MYLIB' and memname='HAVE'
  and name like 'R%CENREG';
  quit;
  
  %put &rename_list;
  
  proc datasets lib=mylib;
  modify have;
  rename &rename_list;
  run;quit;

• Use SQL and dictionary table vcolumn to build the rename list dynamically. The code above is untested, so test it for your data with the correct libname and dataset name. Note they must be uppercase. Check the macro variable resolves correctly
• Use proc datasets to apply the RENAME
• If the variable list is really long (1000+) then you may need a different approach. 

---

@nwang5 wrote:

The original variables include R9CENREG R10CENREG  R11CENREG

I want to change them as:

region9=R9CENREG ;
region10=R10CENREG ;
region11=R11CENREG ;

I was wondering if there is any easier way

---

[nwang5]

Thanks! Can I do several variables in one macro?
I want to change them as:

region9=R9CENREG ;
region10=R10CENREG ;
region11=R11CENREG ;

age9=R9AGE;
age10=R10AGE;
age11=R11AGE;

cogtot9=R9COGTOT;
cogtot10=R10COGTOT;
cogtot11=R11COGTOT;

[SASKiwi]

Just wanted to point out that moving variable name number suffixes into the middle of your names you wont be able to use variable name lists like region9-region11 any more in your code. Personally I find them very useful. 

[Reeza · Accepted solution]

proc sql noprint;
select catt (name, '=', catt('Region', compress(name, , 'kd'))), into :region_list separated by " "
from sashelp.vcolumn
where libname='MYLIB' and memname='HAVE'
and name like 'R%CENREG';
select catt (name, '=', catt('Age', compress(name, , 'kd'))), into :age_list separated by " "
from sashelp.vcolumn
where libname='MYLIB' and memname='HAVE'
and name like 'R%AGE';
select catt (name, '=', catt('COGTOT', compress(name, , 'kd'))), into :cog_list separated by " "
from sashelp.vcolumn
where libname='MYLIB' and memname='HAVE'
and name like 'R%COGTOT';
quit;


proc datasets lib=mylib;
modify have;
rename &region_list. &age_list. &cog_list.;
run;quit;

Sure. 

Create three lists, one for each set of variables and add to the rename list.

[nwang5]

Thanks! You are so nice. But the new code doesn't work. Would you mind helping check it?

[Reeza]

Remove the comma before the into. No data was provide so code is untested.

[nwang5]

Hi Reeza, I hope you are having a great weekend. I used the code you shared with me, but there is something wrong. Would you mind helping me check it? Thanks!

proc sql noprint;
select catt (name, '=', catt('sight', compress(name, , 'kd'))) into : sight_list separated by " "
from sashelp.vcolumn
where libname='LIBRARY' and memname='ELSA' and lowcase(name) like 'r%sight';

select catt (name, '=', catt('dsight', compress(name, , 'kd'))) into : dsight_list separated by " "
from sashelp.vcolumn
where libname='LIBRARY' and memname='ELSA' and lowcase(name) like 'r%dsight';

select catt (name, '=', catt('nsight', compress(name, , 'kd'))) into : nsight_list separated by " "
from sashelp.vcolumn
where libname='LIBRARY' and memname='ELSA' and lowcase(name) like 'r%nsight';
quit;

proc datasets lib=LIBRARY;
modify ELSA;
rename &sight_list. &nsight_list. &dsight_list.;
run;quit;

[Tom]

Don't try to rename two variables to the same name (or the name of some other variable that already exists).

PS Don't post text as photographs.  It makes it harder to read and impossible to copy the text and show you the correction.

[nwang5]

Thank you so much! Really appreciated it. I changed the "sight" to "eyes," but the error is still similar

proc sql noprint;

select catt (name, '=', catt('eyes', compress(name, , 'kd'))) into : eyes_list separated by " "
from sashelp.vcolumn where libname='LIBRARY' and memname='ELSA' and name like 'r%sight';

select catt (name, '=', catt('dsight', compress(name, , 'kd'))) into : dsight_list separated by " "
from sashelp.vcolumn where libname='LIBRARY' and memname='ELSA' and name like 'r%dsight';

select catt (name, '=', catt('nsight', compress(name, , 'kd'))) into : nsight_list separated by " "
from sashelp.vcolumn where libname='LIBRARY' and memname='ELSA' and name like 'r%nsight';

quit;
proc datasets lib=LIBRARY;
modify ELSA;
rename &eyes_list. &nsight_list. &dsight_list.; run;quit;

Log:

999 proc sql noprint;
1000
1001 select catt (name, '=', catt('eyes', compress(name, , 'kd'))) into : eyes_list separated by " "
1002 from sashelp.vcolumn where libname='LIBRARY' and memname='ELSA' and name like 'r%sight';
1003
1004 select catt (name, '=', catt('dsight', compress(name, , 'kd'))) into : dsight_list separated by
1004! " "
1005 from sashelp.vcolumn where libname='LIBRARY' and memname='ELSA' and name like 'r%dsight';
1006
1007 select catt (name, '=', catt('nsight', compress(name, , 'kd'))) into : nsight_list separated by
1007! " "
1008 from sashelp.vcolumn where libname='LIBRARY' and memname='ELSA' and name like 'r%nsight';
1009
1010 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.03 seconds

1011 proc datasets lib=LIBRARY;
1012 modify ELSA;
1013 rename &eyes_list. &nsight_list. &dsight_list.; run;quit;
NOTE: Renaming variable r1sight to eyes1.
NOTE: Renaming variable r2sight to eyes2.
NOTE: Renaming variable r3sight to eyes3.
NOTE: Renaming variable r4sight to eyes4.
NOTE: Renaming variable r5sight to eyes5.
NOTE: Renaming variable r6sight to eyes6.
NOTE: Renaming variable r7sight to eyes7.
NOTE: Renaming variable r8sight to eyes8.
NOTE: Renaming variable r9sight to eyes9.
ERROR: Variable eyes1 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes2 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes3 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes4 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes5 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes6 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes7 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes8 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes9 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes1 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes2 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes3 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes4 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes5 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes6 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes7 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes8 already exists on file LIBRARY.ELSA.
ERROR: Variable eyes9 already exists on file LIBRARY.ELSA.
NOTE: Renaming variable r1nsight to nsight1.
NOTE: Renaming variable r2nsight to nsight2.
NOTE: Renaming variable r3nsight to nsight3.
NOTE: Renaming variable r4nsight to nsight4.
NOTE: Renaming variable r5nsight to nsight5.
NOTE: Renaming variable r6nsight to nsight6.
NOTE: Renaming variable r7nsight to nsight7.
NOTE: Renaming variable r8nsight to nsight8.
NOTE: Renaming variable r9nsight to nsight9.
NOTE: Renaming variable r1dsight to dsight1.
NOTE: Renaming variable r2dsight to dsight2.
NOTE: Renaming variable r3dsight to dsight3.
NOTE: Renaming variable r4dsight to dsight4.
NOTE: Renaming variable r5dsight to dsight5.
NOTE: Renaming variable r6dsight to dsight6.
NOTE: Renaming variable r7dsight to dsight7.
NOTE: Renaming variable r8dsight to dsight8.
NOTE: Renaming variable r9dsight to dsight9.
NOTE: Statements not processed because of errors noted above.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

[Tom]

We cannot help since we cannot see the names of the variables in your dataset, but you can check.

You are mapping more than one variable to the same new name.

For example names of r1sight, r1dsight and r1ndight will all match the pattern r%sight and map to the same variable name of eyes1.