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?
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.
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;
where _NAME_ not in ('_TYPE_' '_FREQ_')
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.