topic Re: How to exclude some names when use proc sql to create macrolist? in SAS Procedures

How to exclude some names when use proc sql to create macrolist?

ybz12003 — Thu, 06 Jul 2017 20:35:31 GMT

Hello:

I have a proc sql to create a macro variable list. However, some names I don't want them in that macro list. For example, the '&varlist' includes 'apple1', 'pear2', 'grape3' and 'cherry4'. However, 'apple1' and 'pear2' should not be in the list. Please advice how to remove them. Also, is there a way create a masterlist for removing list?

Thanks.

%let removelist1=apple1;

%let removelist2=pear2;

proc sql noprint;

select name into :varlist separated by ' '

from test

where anydigit(name) ge 1;

quit;

%put &varlist;

Re: How to exclude some names when use proc sql to create macrolist?

slchen — Thu, 06 Jul 2017 20:05:17 GMT

proc sql noprint;
select name into :varlist separated by ' '
from test where name not in ('apple', 'pear') ;
quit;

Re: How to exclude some names when use proc sql to create macrolist?

LinusH — Thu, 06 Jul 2017 20:07:07 GMT

Just add them to your WHERE clause.
And yes, you can create a look up data set containing those values (one per row) and then use the
NOT IN (SELECT name FROM Lookupdataset)
construct in the WHERE clause as well.