topic Re: Drop multiple variables with different prefix in SAS Programming

Drop multiple variables with different prefix

michokwu — Wed, 02 Sep 2020 18:11:01 GMT

Hello Experts,

I want to drop some variables from a dataset with over 200 variables. The variable names are unique but are grouped using a common prefix/characters.

The following variables table is a sample obtained using the code below. For example, I would like to drop the range of variables starting with AA01 - AA03, variables that start with 'LICAT' and variables containing 'XY' and 'BC'. Thank you.

proc contents data=moi.industry out=moi.names(keep=name) noprint ; run;

NAME

AA08

AA01Z08

AA01Y08

AA02Z08

AA02Y08

AA03Z08

AA03Y08

AA04Z08

AA04Y08

AA05Z08

AA05Y08

BBCY08

BBCZ08

LICATY08

LICATN08

WXY01Y08

WXY01X08

Re: Drop multiple variables with different prefix

PaigeMiller — Wed, 02 Sep 2020 18:13:44 GMT

proc sql noprint;
    select name into :names separated by ' ' from moi.names 
      where name eqt 'LICAT' or name?'XY' or name?'BC';
quit;

Then in a data step

drop &names;

Re: Drop multiple variables with different prefix

michokwu — Wed, 02 Sep 2020 18:54:41 GMT

@PaigeMiller Thank you. It works, except for the range AA01 - AA03.

Re: Drop multiple variables with different prefix

jimbarbour — Wed, 02 Sep 2020 18:55:54 GMT

@michokwu,

For prefixed variables, it's very easy. You can use the colon as a wild card. For example in the data step below, all variables starting with "AA01" - "AA03" and "LICAT."

DATA  moi.names;
    DROP  AA01:;
    DROP  AA02:;
    DROP  AA03:;
    DROP  LICAT:;
    SET  moi.industry;
RUN;

Jim

Re: Drop multiple variables with different prefix

PaigeMiller — Wed, 02 Sep 2020 19:09:32 GMT

@michokwu wrote:

@PaigeMiller Thank you. It works, except for the range AA01 - AA03.

You have the change the WHERE clause in PROC SQL to select variables whose names begin with AA01-AA03. Since I showed you how to delete names that begin with LICAT, I leave deleting names that begin with AA01-AA03 up to you as a homework assignment.

Re: Drop multiple variables with different prefix

jimbarbour — Wed, 02 Sep 2020 19:06:02 GMT

Unfortunately the above answer doesn't take care of "XY" embedded in the middle of a variable. However, since the "XY's" all are prefixed with a "W", you could code:

DATA	Moi.Names;
	DROP  WXY:;	
    DROP  AA01:;
    DROP  AA02:;
    DROP  AA03:;
    DROP  LICAT:;
	SET	Moi.INDUSTRY;
RUN;

Which would give you:

Jim

Re: Drop multiple variables with different prefix

michokwu — Wed, 02 Sep 2020 22:34:36 GMT

@jimbarbour Thanks. Surprisingly, it works for me. I don't really want to write out all the prefixes, that's why I'm trying to use a range of variables

Re: Drop multiple variables with different prefix

jimbarbour — Wed, 02 Sep 2020 22:56:21 GMT

I understand. In that case, it's better to go with what @PaigeMiller suggested. Would you like help with that or do understand what he was suggesting?

However, save the colon wildcard in the back of your mind somewhere. It is a useful technique. If you prefix all your work variables with an underscore, you can code a single drop statement such that no work variables wind up in your Production data. For example:

DATA Prod.Very_Important_Data;
    DROP  _:;
    SET Input.Source_Data;
     _Daily_Amount = Annual_Amount / 365;
    _Standardized_Quarter = (30 * 3);
     Quarterly_Amount = _Daily_Amount * _Standardized_Quarter;
RUN;

The above example is a bit contrived, but in this case _Daily_Amount and _Standardized_Quarter are removed from the final results because of the Drop statement. In a program with dozens of intermediate or work variables, the colon modifier is very handy.

Jim