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.
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;
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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: