topic Re: Filtering using another table in SAS Programming

Filtering using another table

alabchaa — Thu, 03 Oct 2019 14:14:42 GMT

I have 2 SAS tables, both of them have similar IDs. I want to filter table 1 to have only the IDs in table 2.

Re: Filtering using another table

PeterClemmensen — Thu, 03 Oct 2019 14:19:01 GMT

Hi and welcome to the SAS Communtiy 🙂

See if this is applicable to your actual problem. Otherwise, don't hesitate to ask

data table1;
input id var1;
datalines;
1 10
1 20
2 30
2 40
3 50
3 60
;

data table2;
input id var2;
datalines;
1 10
1 20
3 50
3 60
;

data want;
    if _N_=1 then do;
        declare hash h(dataset:'table2');
        h.definekey('id');
        h.definedone();
    end;

    set table1;

    if h.check()=0;
run;

Re: Filtering using another table

yabwon — Thu, 03 Oct 2019 14:32:36 GMT

Hi.

If ID are integers you could try direct addressing

For range up to 1mln it will required ~8MB of RAM, for 10mln it will required ~8oMB, for 100mln it will required ~8ooMB, and so on...

All the best

Bart

data table1;
input id var1;
datalines;
1 10
1 20
2 30
2 40
3 50
3 60
;
run;

data table2;
input id var2;
datalines;
1 10
1 20
3 50
3 60
;
run;

options fullstimer msglevel=i;
%let size = 1000000;
data want;
    array t[&size.] _temporary_; 

    do until(eof1);
      set table2(keep = id) end=eof1;
      t[id] = 1;
    end;

    do until(eof2);
      set table1 end=eof2;
      if t[id] = 1 then output;
    end;
  stop;
run;

Re: Filtering using another table

novinosrin — Thu, 03 Oct 2019 14:38:08 GMT

data table1;
input id var1;
datalines;
1 10
1 20
2 30
2 40
3 50
3 60
;
run;

data table2;
input id var2;
datalines;
1 10
1 20
3 50
3 60
;
run;

proc sql;
create table want as
select *
from table1
where id in (select distinct id from table2);
quit;