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

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User
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.

View solution in original post

5 REPLIES 5
novinosrin
Tourmaline | Level 20

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
Pyrite | Level 9
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
Tourmaline | Level 20

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
Super User
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
Super User

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 639 views
  • 0 likes
  • 3 in conversation