Only rename the variable once. Probably easier if you use a DATA step instead of multiple SQL queries.
proc contents data=library.elsa noprint out=contents; run;
data _null_;
if eof then call execute('run; quit;');
set contents end=eof;
where lowcase(name) like 'r%sight';
call execute('proc datasets lib=library nolist; modify elsa;');
suffix=compress(name,,'kd');
if find(name,'nsight ','i') then newname=cats('nsight',);
else if find(name,'dsight ','i') then newname=cats('dsight',suffix);
else newname=cats('sight',suffix);
call execute(catx(' ','rename',catx('=',nliteral(name),nliteral(newname)),';');
run;
If you did want to use SQL then use the CASE to decide which name to generate, that way only one name will be picked for each variable.
select
catx('=',nliteral(name)
,case
when (lowcase(name) like 'r%nsight') then cats('nsight', compress(name,,'kd'))
when (lowcase(name) like 'r%dsight') then cats('dsight', compress(name,,'kd'))
else cats('sight', compress(name,,'kd'))
end
)
into :rename_list separated by ' '
from sashelp.vcolumn
where libname='LIBRARY'
and memname='ELSA'
and lowcase(name) like 'r%sight'
;
... View more