topic Re: macro list from dataset in SAS Programming

macro list from dataset

drdee — Tue, 28 Jul 2020 11:58:05 GMT

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

Re: macro list from dataset

Kurt_Bremser — Tue, 28 Jul 2020 12:02:02 GMT

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;

Re: macro list from dataset

PaigeMiller — Tue, 28 Jul 2020 12:03:11 GMT

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

Re: macro list from dataset

Kurt_Bremser — Tue, 28 Jul 2020 12:04:18 GMT

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;

Re: macro list from dataset

drdee — Tue, 28 Jul 2020 12:23:26 GMT

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

Re: macro list from dataset

drdee — Tue, 28 Jul 2020 12:24:40 GMT

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