How to rename a wide amount of variables with forbidden characters in their names?

[saslamber]

Hi everyone,

I have a table (named tabletest) with lots of variables (too numerous to be renamed one by one, for instance variables named a-1... a-10, ..., z-1... z-10, a, ..., z) whose names may have invalid characters (for instance an-1, which was coded as 'an-1'n to be accepted). My aim is to rename all these variables with invalid characters (we can assume - is the only one), by replacing the invalid characters by Z.

I figured I would first get the list of the names of all variables from my table by exporting the result of a proc contents in a table named listname (where I only kept the names).

I know I can use tranwrd (for instance name = TRANWRD(name, '-', 'Z') ; in a data set) to rename all elements of my list of variables listname. However, I do not know how to rename all the variables of tabletest that appear to have a - in their name.

I tried what you can see below, but it didn't work because temp1 isn't a variable of tabletest in my code, only the values it takes are ones.

DATA tabletest ;
  INPUT 'an-1'n 'an-2'n 'an-3'n 'an-4'n 'an-5'n 'an-6'n 'an-7'n 'an-8'n b c d e ;
  CARDS ;
1 3 5 7 9 11 13 15 17 19 21 23
2 4 6 8 10 12 14 16 18 20 22 24
;
RUN ;

DATA listname ;
INPUT name : $40. ;
CARDS ;
an-1
an-2
an-3
an-4
an-5
an-6
an-7
an-8
b
c
d
e
;
RUN ;

DATA _NULL_ ;
SET listname ;
temp1 = name ;
temp2 = TRANWRD(name, '-', 'Z') ;
IF temp2 NE temp1 THEN CALL execute('PROC DATASETS LIBRARY = work ; MODIFY tabletest ; RENAME temp1 = temp2 ;') ;
RUN ;

What could I do to fix that issue?

Thanks in advance for any proposal.

[tomrvincent · Accepted solution]

I hope this helps.

proc sql noprint;
select "'"||name||"'n",TRANWRD(name, '-', 'Z') into :name,:newname
from dictionary.columns
where upcase(libname)='WORK' and lowcase(memname)='tabletest' and name like '%-%';
quit;

PROC DATASETS LIBRARY = work ; MODIFY tabletest ; RENAME &name = &newname ;

[ChrisNZ]

Or to rename them all:

proc sql noprint;
  select catt("'",NAME,"'n=",tranwrd(NAME,'-','Z')) into :rename separated by ' '
  from DICTIONARY.COLUMNS
  where LIBNAME='WORK' and MEMNAME='TABLETEST' and NAME contains '-';
quit;
                                                                          
proc datasets noprint ; modify TABLETEST ; rename &rename. ; quit;

[saslamber]

Both of your codes worked to solve my issue, my thanks.