Help using Base SAS procedures

Look up variable in table

Reply
New Contributor
Posts: 2

Look up variable in table

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!

Regular Contributor
Posts: 156

Re: Look up variable in table

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

Regular Contributor
Posts: 156

Re: Look up variable in table

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;
Valued Guide
Posts: 765

Re: Look up variable in table

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;

Respected Advisor
Posts: 3,777

Re: Look up variable in table

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;
Valued Guide
Posts: 858

Re: Look up variable in table

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

Contributor hbi
Contributor
Posts: 66

Re: Look up variable in table

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;

Ask a Question
Discussion stats
  • 6 replies
  • 400 views
  • 2 likes
  • 6 in conversation