Renaming multiple variable names

[dac_js]

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;

[Kurt_Bremser · Accepted solution]

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.

[dac_js]

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

[Kurt_Bremser]

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.

[dac_js]

Thank you Kurt!

[Oligolas]

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;

[dac_js]

Thank you. Your code is working.