How to solve the error?
proc sql noprint;
select catt (name, '=', catt('nsight', compress(name, , 'kd'))) into : nsight_list separated by " "
from sashelp.vcolumn
where libname='LIBRARY' and memname='ELSA'
and name like 'r%nsight';
select catt (name, '=', catt('dsight', compress(name, , 'kd'))) into : dsight_list separated by " "
from sashelp.vcolumn
where libname='LIBRARY' and memname='ELSA'
and name like 'r%dsight';
select catt (name, '=', catt('sight', compress(name, , 'kd'))) into : sight_list separated by " "
from sashelp.vcolumn
where libname='LIBRARY' and memname='ELSA'
and name like 'r%sight';
quit;
proc datasets lib=LIBRARY;
modify ELSA;
rename &nsight_list. &dsight_list. &sight_list.;
run;quit;
Your where clauses are not mutually exclusive.
Your last selection where name like 'r%sight' will also select variable names already selected by like 'r%dsight'
This leads to a duplicated rename statement and though why Proc Datasets tells you with the 2nd attempt that the variable doesn't exist (as already renamed).
You could extend your last where clause to something like where name like 'r%sight' and name not like 'r%nsight' and name not like 'r%dsight'
Alternatively code like below should also work.
proc sql noprint;
select
case
when lowcase(name) like 'r%nsight' then catt(name, '=', catt('nsight', compress(name, , 'kd')))
when lowcase(name) like 'r%dsight' then catt(name, '=', catt('dsight', compress(name, , 'kd')))
else catt(name, '=', catt('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'
;
quit;
Your where clauses are not mutually exclusive.
Your last selection where name like 'r%sight' will also select variable names already selected by like 'r%dsight'
This leads to a duplicated rename statement and though why Proc Datasets tells you with the 2nd attempt that the variable doesn't exist (as already renamed).
You could extend your last where clause to something like where name like 'r%sight' and name not like 'r%nsight' and name not like 'r%dsight'
Alternatively code like below should also work.
proc sql noprint;
select
case
when lowcase(name) like 'r%nsight' then catt(name, '=', catt('nsight', compress(name, , 'kd')))
when lowcase(name) like 'r%dsight' then catt(name, '=', catt('dsight', compress(name, , 'kd')))
else catt(name, '=', catt('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'
;
quit;
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'
;
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.