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

[rajeshV89]

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;

[Kurt_Bremser]

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),

[rajeshV89]

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?

[Kurt_Bremser · Accepted solution]

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.

[rajeshV89]

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