topic Re: What is the way to use joins instead of merge for below code? in SAS Programming

What is the way to use joins instead of merge for below code?

rajeshV89 — Wed, 05 May 2021 12:20:30 GMT

data want;

merge dm(in=a) ae(in=b) cm(in=d) mh(in=d);

by usubjid;

if a and (b or c or d);

run;

Re: What is the way to use joins instead of merge for below code?

Kurt_Bremser — Wed, 05 May 2021 12:30:47 GMT

Try

proc sql;
select a.*
from dm a
where a.usubjid in (
  select distinct usubjid from ae
  union
  select distinct usubjid from cm
  union
  select distinct usubjid from mh
);
quit;

If you need to include additional variables from datasets ae, cm and mh, the whole thing will be a lot more complicated.

The sheer simplicity of the data step code tells you which is the right tool for this task (Maxim 14),

Re: What is the way to use joins instead of merge for below code?

rajeshV89 — Wed, 05 May 2021 12:49:45 GMT

if i want to add any variable from ae or cm or cm ...what i mean .... i want to add usubjid, aeterm from ae..... usubjid, mhterm from mh..... also usubjid, cmtrt from cm ....in this case how should i proceed?

Re: What is the way to use joins instead of merge for below code?

Kurt_Bremser — Wed, 05 May 2021 13:00:57 GMT

Stay with your data step.

SQL (untested) might look like this:

proc sql;
create table want as
  select
    dm.*,
    ae.aeterm,
    cm.cmtrt,
    mh.mhterm
  from dm a
  left join ae
  on dm.usubjid = ae.usubjid
  left join cm
  on dm.usubjid = cm.usubjid
  left join mh
  on dm.usibjid = mh.usubjid
  where a.usubjid in (
    select distinct usubjid from ae
    union
    select distinct usubjid from cm
    union
    select distinct usubjid from mh
  )
;
quit;

But I can only repeat the last line of my previous post. Compare the codes, and you'll see that forcing this unto SQL is just pure idiocy.

Re: What is the way to use joins instead of merge for below code?

rajeshV89 — Wed, 05 May 2021 13:25:06 GMT

It's working fine..thanks for the information..