CODEWITHSUNDEEP
sas
photec
photec

Reputation: 81

Deleting variable names containing specific string

I'm just starting to learn SAS and wanted to see if anyone knew of a way to delete certain variables from a dataset if they contained a certain word. I'm working with a dataset that contains a huge amount of variables (100+) with the word 'Label' in them and am looking to drop these. Unfortunately the word label comes at the end of the variable name, so I can't do a simple drop label:; Obviously I could individually list all the variables to drop, but I just wanted to see if anyone out there knew of a simpler way to accomplish this task. Thanks for reading and for any help you have to offer up.

Upvotes: 4

Views: 7986

Answers (2)

scott
scott

Reputation: 2275

Using a the vcolumn table and proc sql to create a macro variable a macro variable:

proc sql noprint;

    select trim(compress(name))
    into :drop_vars separated by ' '
    from sashelp.vcolumn
    where libname = upcase('lib1')
        and
            memname = upcase('table1')
        and
            upcase(name) like '%LABEL%'
    ;
quit;

%put &drop_vars.;

data table2;
    set table1;
    drop  &drop_vars.;
run;

the proc sql will create a list of all the variables from table1 in library 'lib1' containing label anywhere in the name and put it into the macro variable called drop_vars. (upcase is used to reduce possibility of case causing an issue)

The data step then uses the drop statement and the drop_vars variable to drop all variables in the list.

Note: Make sure you check the output of the %put statement to ensure you do not drop variables you want to keep

Upvotes: 3

Joe
Joe

Reputation: 63434

What you need to do is come up with a dataset that contains the variable names, then create a macro variable containing those you want to drop. There are three (or more) options for the first part:

  • dictionary.columns
  • sashelp.vcolumn
  • proc contents output to a dataset

All three give the same result - a dataset of variable names (and other things), which you can then query.

So for example, using PROC SQL's SELECT INTO functionality to create a macro variable:

proc sql;
select name into :droplist separated by ' '
 from dictionary.columns
 where libname='SASHELP' and memname='CLASS'
 and name like '%eigh%';
quit;

(replace eigh with Label for your needs; % is wildcard here)

and then you have a macro variable &droplist, which you can then use in a drop statement.

data want;
set sashelp.class;
drop &droplist;
run;

Upvotes: 3

Related Questions