Desktop productivity for business analysts and programmers

Delete columns whose name contains a certain string (macro)

Reply
Frequent Contributor
Posts: 80

Delete columns whose name contains a certain string (macro)

[ Edited ]

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.

 

Super User
Posts: 11,114

Re: Delete columns whose name contains a certain string (macro)

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;
Valued Guide
Posts: 505

Re: Delete columns whose name contains a certain string (macro)

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

 

Frequent Contributor
Posts: 80

Re: Delete columns whose name contains a certain string (macro)

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.

Ask a Question
Discussion stats
  • 3 replies
  • 352 views
  • 1 like
  • 3 in conversation