topic Re: Dropping &varlist within a macro in SAS Programming

Dropping &varlist within a macro

ngnikhilgoyal — Tue, 20 Jan 2015 17:06:17 GMT

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 — Tue, 20 Jan 2015 17:22:42 GMT

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;

ballardw — Tue, 20 Jan 2015 17:26:47 GMT

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.

Ksharp — Wed, 21 Jan 2015 06:56:58 GMT

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