Dropping &varlist within a macro

[ngnikhilgoyal]

This question is a continuation of a question I have asked earlier but it is a different problem hence I've started a new discussion.

I wanted to eliminate a list of variables from my data set. The below code is part of a macro.

proc sql noprint;

select strip(_NAME_) into : varlist separated by ' '

     from to_drop

     where _NAME_ ne '_TYPE_';

     where _NAME_ ne '_FREQ_';

quit;

data inputds_factor;

   set &input (drop=&varlist);

run;

When the above steps execute, I get the error below:

ERROR: The following columns were not found in the contributing tables: _NAME_.

WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

When I execute the code, outside the macro, I don't get this error. How can I overcome this problem?

[art297]

I'd think you'd get the error both inside and outside a macro. You have an extra semi-colon and 2 where statements.

Try:

proc sql noprint;

select strip(_NAME_) into : varlist separated by ' '

   from to_drop

     where _NAME_ ne '_TYPE_' and

           _NAME_ ne '_FREQ_'

  ;

quit;

[Ksharp]

  where _NAME_ not in  ('_TYPE_'  '_FREQ_')

[ballardw · Accepted solution]

Have you done a %put &varlist in the macro to see what the value of the list is? What does it look like?

Also you could suppress those two variables at creation time if desired and using proc means or summary with dataset options:

output out=to_drop (drop=_TYPE_ _FREQ_) <rest of output options> ;

How the data set to_drop is built may have something to do with the result.