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;
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.
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.
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).
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;
- Cheers -
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.