macro list from dataset

[drdee]

Hi all, 

I have a (small) dataset with a datavariable x, of type string. I want to use its values to select data from another (big) set. Doing an inner join as follows seems to be too time-consuming.

proc sql;
create table aa as
select
a.*
from big a
inner join small b
on a.x = b.x
;
quit;

Is there a better way? For example, first put the x values from the small set into a macro list, and then subset on the big one using a where statement.

Thanks in advance.

Dimitri

[Kurt_Bremser]

Use a hash object. No sorting required (the hash sorts internally), will be fastest.

data aa;
set big;
if _n_ = 1
then do;
  declare hash b (dataset:"small (keep=x)");
  b.definekey('x');
  b.definedone();
end;
if b.check() = 0;
run;

[PaigeMiller]

If I am understanding you properly, you want to use a RIGHT JOIN instead of an INNER JOIN

[drdee]

Hi Paige,

Thanks for your reply! Yes, it may also be useful to see when some x values are not found in the big set.

Best regards,
Dimitri

[Kurt_Bremser · Accepted solution]

Instead of the inner join, you can use a WHERE clause in SQL:

proc sql;
create table aa as
  select *
  from big
  where x in (select distinct x from small)
;
quit;

[drdee]

Hi Kurt,

Many thanks for your quick replies! Both work perfectly. I accepted the second as it is easier to understand (for me) what happens.

Best regards,
Dimitri