Look up variable in table

[Olaade]

Please,

How do I re-write this code:

data sample;

          Set new;

          if var in  ('1', '2', '3') then delete;

run;

I need the '1','2','3' to be in a permanent file/table, so that the "var" can reference a column in the file and execute the command (ie I don't want 1,2,3 to show on my SAS code.

Thanks!

[PhilC]

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition,  SET Statement, Example 7: Performing a Table Lookup

[PhilC]

This is my idea more precisely.  M.Johnson is correct:  Many ways to skin this cat.

data a (index=(x));
x=1;output;
x=2;output;
x=3;output;
stop;
run;

data b;
infile datalines;
input x y;

datalines;
1 12
1 12
3 90
4 90
2 89
5 34
1 89
;
run;

data c;
  set b;
  set a (in=found) key=x;
  if found;
run;

[MikeZdeb]

Hi ... maybe get rid of the error messages in the LOG ...

data c;

  set b;

  set a key=x;

  if ^_error_ then output; else _error_ = 0;

run;

[data_null__]

I think we also need UNIQUE option for KEY= and NOT FOUND.

data c;
   set b;
   set a (in=found) key=x/unique;
   _error_=0;
   if not found;
   run;

[Steelers_In_DC]

I'm sure there are plenty of ways, here is one:

data ref;

input var$;

cards;

1

2

3

;

run;

data table;

input var$;

cards;

1

2

3

4

5

;

run;

proc sql;

create table want as

select *

from table

where var not in (

    select var from ref);

[hbi]

Here is yet another way to skin the cat. This one uses SAS' hash object. In my experience, the hash object is versatile for a fairly wide variety of lookups, unless the lookup table is excessively large and/or the lookup keys are very very long character variables.

I have reused PhilC's sample datasets "a" and "b".

data a (index=(x));

  x=1;output;

  x=2;output;

  x=3;output;

  stop;

run;

data b;

  infile datalines;

  input x y;

  datalines;

1 12

  1 12

  3 90

  4 90

  2 89

  5 34

  1 89

;

run;

data hash_example(drop=rc);

  set b;

  length rc 8 found $3;

  /* use a SAS hash object to do the lookup; it is fast for most tasks! */

  if _n_ = 1 then do;

    declare hash t(dataset: 'a');

    t.definekey('x');

    t.definedone();

  end;

  rc=T.find();

  if rc=0 then found='Y';

  else do;

    found='N';

    put "value x=" x " not found in the lookup table!!";

  end;

run;