topic Re: merge (while 1 has single record others has copies of many with out using prc sort before) in SAS Programming

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

sahoositaram555 — Fri, 22 May 2020 17:20:41 GMT

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

012 yes 130 89 71

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.

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

novinosrin — Fri, 22 May 2020 17:29:38 GMT

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;

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

ballardw — Fri, 22 May 2020 17:32:36 GMT

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.

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

sahoositaram555 — Fri, 22 May 2020 17:37:06 GMT

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.

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

novinosrin — Fri, 22 May 2020 17:43:06 GMT

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

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

ballardw — Fri, 22 May 2020 19:30:16 GMT

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.