topic Re: Renaming multiple variable names in SAS Programming

Renaming multiple variable names

dac_js — Thu, 20 Jul 2023 03:36:26 GMT

Hi,

I have the following dataset:

data have;
input ID Var1 Var2 Var3new Var4new Var5old Var6old;
datalines;
1 11 12 13 14 15
16 2 21 22 23 24
25 26 3 31 32 33
34 35 36 4 41 42
43 44 45 46 5 56
51 52 53 54 55 56
;
run;

First I want a dataset after dropping all variables with names ending with "old" from the dataset (Var5old, Var6old) and in the new dataset I want to rename the variables where column name ends with "new" (Var3new, Var4new as Var3 Var4). As I have 100s of variables do not want to manually rename all of them. I am looking for a final dataset:

data want;
input ID Var1 Var2 Var3 Var4;
datalines;
1 11 12 13
16 2 21 22
25 26 3 31
34 35 36 4
43 44 45 46
51 52 53 54
;
run;

Re: Renaming multiple variable names

Kurt_Bremser — Thu, 20 Jul 2023 06:43:08 GMT

data _null_;
set sashelp.vcolumn end=done;
where libname = "WORK" and memname = "HAVE";
length renamestr dropstr $32767;
retain renamestr dropstr;
if upcase(substr(name,length(name)-2)) = "OLD" then dropstr = catx(" ",dropstr,name);
if upcase(substr(name,length(name)-2)) = "NEW" then renamestr = catx(" ",renamestr,catx("=",name,substr(name,1,length(name)-3)));
if done
then do;
  call symputx("renamestr",renamestr);
  call symputx("dropstr",dropstr);
end;
run;

data want;
set have;
drop &dropstr.;
rename &renamestr.;
run;

Untested, posted from my tablet.

Re: Renaming multiple variable names

Oligolas — Thu, 20 Jul 2023 07:45:57 GMT

DATA _NULL_;
   set sashelp.vcolumn end=last;
   where libname eq 'WORK' and memname eq 'HAVE';
   if _N_ eq 1 then call execute('DATA want; set have;');
   if prxmatch('/old\s*$/i',name) then call execute('drop '||name||';');
   if prxmatch('/.+new\s*$/i',name) then call execute(catx(' ','rename',name,'=',substrn(name,1,lengthn(name)-3),';'));
   if last then call execute('run;');
RUN;

Re: Renaming multiple variable names

dac_js — Thu, 20 Jul 2023 12:39:33 GMT

Thanks! Its working. Could you please tell me the purpose of referring to sashelp.vcolumn ?

Re: Renaming multiple variable names

dac_js — Thu, 20 Jul 2023 12:40:58 GMT

Thank you. Your code is working.

Re: Renaming multiple variable names

Kurt_Bremser — Thu, 20 Jul 2023 13:00:54 GMT

SASHELP.VCOLUMN is a SQL view using the pseudo-table DICTIONARY.COLUMNS (which itself is a dynamically created view of all variables in all datasets/views in all currently assigned libraries).

See the documentation of the DICTIONARY tables.

Re: Renaming multiple variable names

dac_js — Thu, 20 Jul 2023 13:37:56 GMT

Thank you Kurt!