topic Re: how to select rows with column value not in a macro list? in SAS Programming

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

Geo- — Thu, 06 Sep 2018 10:07:55 GMT

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

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

Kurt_Bremser — Thu, 06 Sep 2018 10:12:38 GMT

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

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

Kurt_Bremser — Thu, 06 Sep 2018 10:02:33 GMT

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

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

RW9 — Thu, 06 Sep 2018 10:06:17 GMT

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#M129465

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.

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

Geo- — Thu, 06 Sep 2018 10:09:43 GMT

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

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

RW9 — Thu, 06 Sep 2018 10:24:08 GMT

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

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

Geo- — Thu, 06 Sep 2018 10:26:38 GMT

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

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

RW9 — Thu, 06 Sep 2018 10:57:27 GMT

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

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

Geo- — Thu, 06 Sep 2018 11:18:03 GMT

handle variable is quite difficult sometime in sas

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

Kurt_Bremser — Thu, 06 Sep 2018 11:46:04 GMT

@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.