topic Re: Look up values in table instead of list in SAS Programming

Look up values in table instead of list

Olaajay — Tue, 25 Aug 2015 21:10:06 GMT

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).

Re: Look up values in table instead of list

Ksharp — Wed, 26 Aug 2015 12:52:46 GMT

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

Re: Look up values in table instead of list

Astounding — Wed, 26 Aug 2015 12:52:57 GMT

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.

Re: Look up values in table instead of list

Olaajay — Wed, 26 Aug 2015 15:38:39 GMT

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