how to select rows with column value not in a macro list?

[Geo-]

how to select  rows with column value not in  a macro list?

%macro changeName(&var_list);
proc sql noprint;
 select cats(name,'=',case when type='num' then 'XN_' else 'XC_' end,name) into :renames separated by ' '
 from dictionary.columns where libname='SASHELP' and memname='CLASS' and name not in(&var_list);
%put &renames;
data test;
 set sashelp.class;
 rename &renames;
run;
%mend();

%changeName(Age,Name);



  

[Kurt_Bremser]

Take my suggestion from the other thread, and expand it:

data _null_;
set sashelp.vcolumn (where=(libname="LIBRARY" and memname = "DATASET")) end=eof;
length prefix $3;
if _n_ = 1 then call execute("
  proc datasets library=LIBRARY nolist;
  modify DATASET;
  rename
");
if name not in (&var_list.)
then do;
  if type = 'char'
  then prefix = "XC_";
  else prefix = "XN_";
  call execute(trim(name !! '=' !! prefix !! trim(name) !! ' '));
end;
if eof then call execute("
  ;
  quit;
");
run;

Edit: corrected in-list

[Kurt_Bremser]

Note that I prefer to use proc datasets for the renaming action because it does not rewrite the whole dataset, only the header page.

[RW9]

What does num_list look like?  What is the end goal of the code?  

If num_list is a space delimited list then there was this exact post yesterday:

https://communities.sas.com/t5/SAS-Programming/Want-to-quote-the-string-in-macro-variable/m-p/492585...

However, depending on your process its likely you don't need any of this code.  Also, again, please provide full information, test data, required output.  Its written under the Post question button, and saves us having to guess.

[Geo-]

I have updated the case,would you mind check if it's clear to you

[RW9]

Thanks, after looking at that, the simplest method is probably:

%macro changename(dsin=,dsout=,varlist=);

  data &dsout.;
    set &dsin.;
    rename 
    %do i=1 %to %sysfunc(countw(&varlist.,|));
      %scan(&varlist.,&i.,|)=%scan(&varlist.,&i.,|)_want 
    %end;
    ;
  run;
  
%mend changename;

%changename(dsin=sashelp.class,dsout=want,varlist=age|name);

I use the | to delimit rather than a comma as the comma is used to divide paramters, so best avoided.  I show as datastep here, but once you understand the above code, proc datasets should be used as that is quicker.

Edit, the same thing but with proc datasets, modifying in place:

%macro changename(dsin=,varlist=);

  proc datasets lib=work nolist;
    modify &dsin.;
      rename 
        %do i=1 %to %sysfunc(countw(&varlist.,|));
           %scan(&varlist.,&i.,|)=%scan(&varlist.,&i.,|)_want 
        %end;
      ;
  quit;
  run;
       
%mend changename;

data want;
  set sashelp.class;
run;

%changename(dsin=want,varlist=age|name);

[Geo-]

actually I want to change the names which are not in the macro variables..

[RW9 · Accepted solution]

Bit more complicated then:

%macro changename(dsin=,varlist=);

  proc sql;
    select cats(name,"=",name,"_want")
    into :tmp separated by " "
    from dictionary.columns
    where libname="WORK"
      and memname=upcase("&dsin.")
      and upcase(name) not in (
    %do i=1 %to %sysfunc(countw(&varlist.,|));
      %sysfunc(quote(%sysfunc(upcase(%scan(&varlist.,&i.,|)))))
    %end; 
      );
  quit;

  proc datasets lib=work nolist;
    modify &dsin.;
      rename &tmp.;
  quit;
  run;

  
%mend changename;

data want;
  set sashelp.class;
run;

%changename(dsin=want,varlist=age|name);

[Geo-]

handle variable is quite difficult sometime in sas

[Kurt_Bremser]

---

@Geo- wrote:
handle variable is quite difficult sometime in sas

---

It should be a rather rare occurence. Data is fluent, while structure should be fixed. Changing column names are usually an indicator for poor design.