Delete columns whose name contains a certain string (macro)

[ayin]

I want to write a macro, by using which some columns, if the column name contains a certain string, will then be deleted/dropped.

For example, one of the dataset will be like:

          LIB.Original_1

ID       vector_A        vector_B        factor_C

1         a                         b                   c

2         a2                       b2                 c2

after macro, it should be something like:

          LIB.Page_1

ID          factor_C

1                  c

2                  c2

The macro would be something like:

%macro dropCol(_name=);

....

proc sql;

select name into :droplist separated by ' '

    from dictionary.columns

    where libname = 'LIB' and memname = 'Original_'&_name

    and name like '%vector%';

quit;

data LIB.Page_&_name;

set LIB.Original_&_name;

          drop &droplist;

run;

...

%mend dropCol;

%dropCol(_name=1);

%dropCol(_name=2);

%dropCol(_name=3);

%dropCol(_name=4);

However it's not working at the moment. The error reported currently comes from '&droplist'.

It'd be great if someone can fix the codes above or provide other ways of doing the task.

The Error is:

41           proc sql;  select name into :droplist separated by ''  from dictionary.columns  where libname = 'input' and memname =
41       ! "final_&_name"  and name like '%Est_%'; quit;  data work.temp_&_name; set input.final_&_name;  drop &droplist; run;
                                                                                                               _
                                                                                                               22
2                                                          The SAS System                           08:47 Thursday, February 9, 2017

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.  

NOTE: Line generated by the invoked macro "REMOVEE".
41           proc sql;  select name into :droplist separated by ''  from dictionary.columns  where libname = 'input' and memname =
41       ! "final_&_name"  and name like '%Est_%'; quit;  data work.temp_&_name; set input.final_&_name;  drop &droplist; run;
                                                                                                               _
                                                                                                               200
ERROR 200-322: The symbol is not recognized and will be ignored.

MPRINT(REMOVEE):   set input.final_2016;
WARNING: Apparent symbolic reference DROPLIST not resolved.
MPRINT(REMOVEE):   drop &droplist;
MPRINT(REMOVEE):   run;

WARNING: The variable droplist in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEMP_2016 may be incomplete.  When this step was stopped there were 0 observations and 42 variables.
WARNING: Data set WORK.TEMP_2016 was not replaced because this step was stopped.

[ballardw]

Since your Proc sql generates error you really should post the log with the error. Use OPTIONS MPRINT to show more details in the log.

Try

proc sql;
    select name into :droplist separated by ' '
    from dictionary.columns
    where libname = 'LIB' and memname = "Original_&_name" /*<= the ' before name was proximate cause of error*/
                                                          /* place inside double quotes "  " to resolve the macro varialbe*/
    and name like '%vector%';
quit;

[rogerjdeangelis]

Data want;
  set have(drop=vector:);
run;quit;

[ayin]

Hey it works for columns names like: vector_4, vectorETC.

Would you kindly provide a solution for names like ETC_vector_ETC, ETC_vector?

Thanks.

[chalita]

If the string is a common prefix, approach this like a drop statement, but just use a colon after the prefix

data table2;
set table;
drop column_prefix: ;
run;

[Tom]

You should make sure to set DROPLIST empty before the query in case the SELECT statement does not find ANY variables.

The DROP statement does not like it when no variables are listed. But the DROP= dataset option doesn't mind.

Note that the LIBNAME and MEMNAME variable in the dictionary tables are always in uppercase. So you will never find any records where 

libname = 'input' and memname = "final_&_name" 

Plus I doubt that you have any member names with an & in their names. That would be very hard to create and difficult to reference.

Also note that the NAME field will store the names in mixed case.  So a name like Vector1 or VECTOR2 will never match:

name like '%vector%'

Try something like this instead:

proc sql noprint;
%let droplist=;
select name
  into :droplist separated by ' '
from dictionary.columns
where libname = 'LIB'
  and memname = "ORIGINAL_%upcase(&_name)"
  and lowcase(name) like '%vector%'
;

quit;

data LIB.Page_&_name;
  set LIB.Original_&_name(drop=&droplist);
run;