how to exclude some variables from a list of variables

[Sunny_Sun]

Hi,

I need to exclude some variables from a self-defined variable list. that is,

%LET large=a b c d e f g h i j k;

%LET exclude=c d h k;

the variable list I want to have is a b e f g  i j so that I can call in them for later steps.

In Stata, there is a function like:

local finalvar: list large - exclude

Then later on I can just call in finalvar.

is there any similar step in SAS?

Thanks in advance.

Thanks,

Sunny

[Haikuo · Accepted solution]

%LET large=a b c d e f g h i j k;

%LET exclude=c d h k;

%let final=%sysfunc(compress(&large, %sysfunc(compress(&exclude.))));

%put final=&final;

[Sunny_Sun]

This is so cool! Many thanks!

[Haikuo]

Be aware that this only works on characters as you presented, if you have strings, it gets more complicated.

[data_null__]

If you want to work with variable lists in the context of variables and not simply a string of words you need some help from SAS.  PROC TRANSPOSE although a seeming unlikely candidate is the perfect tool.

%macro
   expand_varlist /*Returns an expanded variable list and optionally creates an indexed data set of the variable names*/
      (
         data    = _LAST_, /*Input data*/
         var     = _ALL_,  /*Variable List expanded*/
         where   = 1,      /*Where clause to subset OUT=, useful for selecting by a name suffix e.g. where=_name_ like '%_Status'*/
         outexpr = nliteral(&name), /*An expression that can be used to modify the names in the expanded list*/
         keep    = ,       /*Keep data set option for DATA=*/
         drop    = ,       /*Drop data set option for DATA=*/
         out     = ,       /*Output data indexed by _NAME_ and _OBS_*/
         name    = _NAME_, /*Name of the variable name variable in the output data set*/
         label   = _LABEL_,/*Name of the variable name label variable in the output data set*/
         obs     = _OBS_,  /*Name of the variable index variable in the output data set*/
         dlm     = ' '     /*List delimiter*/
      );
   %if %sysevalF(&sysver lt 9.3,boolean) %then %do;
      %put NOTE: Macro &sysmacroname requires SAS version 9.3 or higher;
      %return;
      %end;
   %local m;
   %let m=&sysmacroname;
   /*Used to insure unique name for the variable altered by statements executed by DOSUBL*/
   /*https://listserv.uga.edu/cgi-bin/wa?A2=ind1501e&L=SAS-L&P=6111*/
   %do %while(%symexist(&m));
      %do %while(%symlocal(&m));
         %let m=&m.&sysindex;
         %end;
      %do %while(%symglobl(&m));
         %let m=&m.&sysindex;
         %end;
      %end;
   %local rc &m code1 code2 code3 code4;
   %if %superq(out) ne %then %let code3 = %str(data &out(index=(&obs &name)); set &out; &obs+1; run;);
   %else %do;
      %let out=%str(work._deleteme_);
      %let code3 = %str(proc delete data=work._deleteme_; run;);
      %end;
   %let code1 = %str(options notes=0; proc transpose name=&name label=&label data=&data(obs=0 keep=&keep drop=&drop) out=&out(where=(&where)); var &var; run;);
   %let code2 = %str(proc sql noprint; select &outexpr into :&m separated by &dlm from &out; quit;);
   %let code4 = %str(options notes=1;);
   %let rc=%sysfunc(dosubl(&code1 &code2 &code3 &code4));
   %put NOTE: Macro(&sysmacroname) retured: %qsysfunc(quote(%superq(&m)));
&&&m.

%mend expand_varlist;

data class;
   set sashelp.class(obs=2);
   retain a b c d e 'x' f g h i j k 1;
   run;


%LET large=a b c d e f g h i j k name age;
%LET exclude=c d h k age;

%let subset = %expand_varlist(data=class,var=&exclude,outexpr=quote(strip(_name_)));
%let new    = %expand_varlist(data=class,var=&large,where=_name_ not in(&subset));
%put NOTE: List "&large" with "&exclude" removed is "&new";

[Sunny_Sun]

wow really appreciated!

[Ksharp]

%LET large=a b c d e f g h i j k;

%LET exclude=c d h k;

%let want=%sysfunc(prxchange(s/%sysfunc(translate(&exclude,%str(|),%str( )))//,-1,&large));

%put &want;

[BOBSAS]

I think, It would work if its single word like a,b,c,d,e

What if the column names are age sex race. Compress will remove a from age and e from sex and a,c,e from race.

proc contents data=sashelp.class out=class;

run;

proc sql;

select name into :include separated by " " from class;

quit;

%put &include;

%let exclude=weight;

%let want=%sysfunc(compress(&include,%sysfunc(compress(&exclude))));

%put &want;

Can Anyone explain how to make a change to the below code to work for a string of words..I see a macro but don't understand it. Looking for some simple answer..Thanks.

[Ksharp]

How about :

%LET large=ab ba b cat d e f g h i j k;

%LET exclude=cat b h k;

%let want=%sysfunc(prxchange(s/\b%sysfunc(tranwrd(&exclude,%str( ),%str(\b|\b)))\b//,-1,&large));

%put &want;

[BOBSAS]

Thanks Keshan. Much appreciated. I have never used prx functions. Do you mind explaining us, how it works! Thanks.

[Ksharp]

Oh. That is a very long story need to talk . You'd check the SAS documentation about PRX .

Or. You could ask Patrick who can explain it better than me .

[BOBSAS]

Awesome. That worked..:) Much appreciated.

[RayJeanClaude]

For those SAS users who are not familiar with (quite complex) PRX functions, here is a little piece of more classical SAS code I built for excluding some variables from a list of variables:

* extracting the variables' names list (these names are: Name Sex Age Height Weight);

ODS OUTPUT PositionShort=ps;
  PROC CONTENTS DATA=sashelp.class VARNUM SHORT;
RUN;QUIT;

* creating a macrovariable containing this list;
DATA _NULL_;
  SET ps;
   CALL SYMPUT ('string', variables);
RUN;

* specifying the list of words (ie variables'names) to be excluded;
    %LET exclude= Age Weight;

* how many words to be excluded;
    %LET words_number=%SYSFUNC(COUNTW(&exclude));

* iteratively excluding undesirable words;
%MACRO m;
    %DO number_in_list=1 %TO &words_number;

        /* selects one word in turn */ 
        %LET word=%SYSFUNC(SCAN(&exclude, &number_in_list));

        /* excludes that word */
        %LET string=%SYSFUNC(TRANWRD(&string, &word,));
    %END;
%MEND;
%m;

%PUT &string;

Hope it is helpful

           JC

[Sunny_Sun]

cool! Thanks.

[BOBSAS]

Hey. Did you get a solution for the above. If so, Please share it.