DATA Step, Macro, Functions and more

Look up values in table instead of list

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Look up values in table instead of list

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


Accepted Solutions
Solution
‎08-26-2015 08:52 AM
Super User
Posts: 10,020

Re: Look up values in table instead of list

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

View solution in original post


All Replies
Solution
‎08-26-2015 08:52 AM
Super User
Posts: 10,020

Re: Look up values in table instead of list

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

New Contributor
Posts: 4

Re: Look up values in table instead of list

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

Super User
Posts: 5,498

Re: Look up values in table instead of list

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 233 views
  • 0 likes
  • 3 in conversation