topic Re: PROC SQL in SAS Programming

PROC SQL

nwang5 — Sat, 18 Feb 2023 04:02:29 GMT

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;

Re: PROC SQL

Patrick — Sat, 18 Feb 2023 04:52:20 GMT

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;

Re: PROC SQL

Tom — Sat, 18 Feb 2023 15:12:09 GMT

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'
;