Help using Base SAS procedures

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

Reply
Super Contributor
Posts: 297

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

[ Edited ]

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;

Super Contributor
Posts: 275

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

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

Super User
Posts: 5,257

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

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.
Data never sleeps
Ask a Question
Discussion stats
  • 2 replies
  • 125 views
  • 2 likes
  • 3 in conversation