Look up values in table instead of list

[Olaajay]

Please how do I re-write the code below:

DATA sample;

Set new;

  IF var in('1','2','3') THEN DELETE ;

Run;

So that, 1,2,3 is located in a table/permanent file and “var” is referencing a column in that table (ie I don’t want my code above to show 1,2,3 upfront).

[Ksharp · Accepted solution]

1) Hash Table.

2) proc format

3) Array

4) SQL

5) IML code

Here is a SQL code.

proc sql;

create table sample as

select * from new

where var not in ( select key from key_table );

quit;

Xia Keshan

[Olaajay]

Thank you so much Xia, it worked! Thanks all!

[Astounding]

SAS supports many versions of table look-up:  merging, formats, hash tables, SQL.  Somebody will have to correct my syntax here, since my SQL is weak.  But something alone these lines would do it:

proc sql;

create table sample as select * from new

   /* some kind of union here with the other table to remove matches */

;

quit;

Sorting and merging would be simple, but requires sorting first.