topic Re: how to exclude some variables from a list of variables in SAS Procedures

how to exclude some variables from a list of variables

Sunny_Sun — Tue, 10 Mar 2015 18:13:27 GMT

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

Re: how to exclude some variables from a list of variables

Haikuo — Tue, 10 Mar 2015 18:48:18 GMT

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

Re: how to exclude some variables from a list of variables

Sunny_Sun — Tue, 10 Mar 2015 18:52:55 GMT

This is so cool! Many thanks!

Re: how to exclude some variables from a list of variables

Haikuo — Tue, 10 Mar 2015 18:55:33 GMT

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

Re: how to exclude some variables from a list of variables

data_null__ — Tue, 10 Mar 2015 20:11:24 GMT

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

Re: how to exclude some variables from a list of variables

Ksharp — Wed, 11 Mar 2015 13:02:01 GMT

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

Re: how to exclude some variables from a list of variables

BOBSAS — Wed, 11 Mar 2015 18:24:15 GMT

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.

Re: how to exclude some variables from a list of variables

Ksharp — Thu, 12 Mar 2015 08:56:02 GMT

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;

Re: how to exclude some variables from a list of variables

BOBSAS — Fri, 13 Mar 2015 03:28:35 GMT

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

Re: how to exclude some variables from a list of variables

BOBSAS — Fri, 13 Mar 2015 03:30:08 GMT

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

Re: how to exclude some variables from a list of variables

Ksharp — Fri, 13 Mar 2015 08:50:14 GMT

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 .

Re: how to exclude some variables from a list of variables

Sunny_Sun — Fri, 13 Mar 2015 14:11:27 GMT

wow really appreciated!

Re: how to exclude some variables from a list of variables

Sunny_Sun — Fri, 13 Mar 2015 14:12:40 GMT

cool! Thanks.

Re: how to exclude some variables from a list of variables

BOBSAS — Fri, 13 Mar 2015 14:40:12 GMT

Awesome. That worked..:) Much appreciated.

Re: how to exclude some variables from a list of variables

RayJeanClaude — Wed, 06 Jul 2016 17:29:25 GMT

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