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.
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 ;
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 ;
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;
Both of your codes worked to solve my issue, my thanks.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: