BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rajeshV89
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

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
Fluorite | Level 6
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
Super User

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
Fluorite | Level 6
It's working fine..thanks for the information..

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 390 views
  • 0 likes
  • 2 in conversation