topic Re: Look up variable in table in SAS Procedures

Look up variable in table

Olaade — Tue, 25 Aug 2015 20:13:26 GMT

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!

Re: Look up variable in table

PhilC — Fri, 28 Aug 2015 14:05:05 GMT

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

Re: Look up variable in table

Steelers_In_DC — Fri, 28 Aug 2015 14:16:07 GMT

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

data ref;

input var$;

cards;

;

run;

data table;

input var$;

cards;

;

run;

proc sql;

create table want as

select *

from table

where var not in (

select var from ref);

Re: Look up variable in table

PhilC — Fri, 28 Aug 2015 14:42:38 GMT

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;

Re: Look up variable in table

MikeZdeb — Fri, 28 Aug 2015 14:54:30 GMT

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;

Re: Look up variable in table

data_null__ — Fri, 28 Aug 2015 15:23:02 GMT

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;

Re: Look up variable in table

hbi — Fri, 28 Aug 2015 17:47:08 GMT

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

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;