merge (while 1 has single record others has copies of many with out using prc sort before)

[sahoositaram555]

Hi experts,

I've 2 datasets,  TABLE "AA" looks like: 

id     WT

011 84.9  

012 76.9  

013 54.9  

TABLE "BB looks like"

id      record  sysbp diabp hr

011   yes     133      87    73  

011   yes     133      87    73

011   yes     133      87    73

012   yes     130      89    71  

012   yes     130      89    71

012   yes     130      89    71

013   yes     121      77    83  

013   yes     121      77    83

013   yes     121      77    83

i would like to merge them so that the final data set should have(as we have the unique values in triplicates in "BB" table)

id     WT   sysbp  diabp  hr

011 84.9  133      87       73  

012 76.9  130      89       71

013 54.9  121      77       83

please let me know if the result table can be achieved with out need of a prior or post nodupkey procedure?is there a direct option available in data step or by proc sql can we achieve it in a single step?

for merge i do use if a=1 as i write merge aa(in=a) bb(in=b). 

Any suggestions are welcome.

[novinosrin]

Hi I am not sure what's the challenge in this task as it seems much too straight forward

data aa;
input id $    WT;
cards;
011 84.9  
012 76.9  
013 54.9  
;

data bb;
input id $  record $ sysbp diabp hr;
cards;
011   yes     133      87    73  

011   yes     133      87    73

011   yes     133      87    73

012   yes     130      89    71  

012   yes     130      89    71

012   yes     130      89    71

013   yes     121      77    83  

013   yes     121      77    83

013   yes     121      77    83
;

data want;
merge aa bb;
by id;
if first.id;
run;

 Or even with SQL

proc sql;
create table want as
select distinct aa.*,record,sysbp,diabp, hr
from aa,bb
where aa.id=bb.id;
quit;

[sahoositaram555]

Hi, Thank your very much. There is a challenge. In BB i have many other id's which i dont want them in my final dataset , so with ur solution for data step probaly i can define in= procedure for both the datasets and can do if a=1 and first.id, but how to the similar thing in sql is something that i'm not aware of.It would be great to have ur reply.

[novinosrin]

Alright, never mind. I give in to you that you are correct. The way I understand you want all the ID's in AA  whether or not it matches with those in BB. I would like you to tell what kind of JOIN/LOOK UP would you consider?

I believe once the above is clear, that should pave way for a right syntax or SAS/SQL words

[ballardw · Accepted solution]

proc sql;
create table want as
select distinct aa.*,record,sysbp,diabp, hr
from aa   
        left join
        bb
 on aa.id=bb.id;
quit;

The Left Join says you want all the records from AA (the set that comes before Left) and matches the second set only when the ON condition is met.

A Where is applied to results of previous steps.

[ballardw]

You really should include an example of the desired output if any of the records in "BB" have different values of sysbp diabp hr for an Id value.

Which I strongly suspect is more likely given that the variables appear to be related to blood pressure readings.