Merge 2 datasets, many to many Match Merge

[CharlesR]

Hello,
I know I can do a one-to-many match merge, but can I do a many to many match merge?

Essentially, i have 3 variables to match by, but here's where things get tricky: some of the observations from one set will not have corresponding matches in the second, while other observations will have multiple matches to the second set. The following three sets represent what i'm referring to, with the first two the sets to merge and the last what i want the final set to look like. I think i can do this with proc sql, but am not sure how.

With this first set, the first 2 variables don't appear in the second set. Then come the 3 variables to match by.
The next set starts with the 3 variables to match by, then the variables to add to the final set.

The final set shows how the observations in the first set, where PRP='Out' have no values from fbp and Percent from the second. The other observations, where PRP='Hit' then duplicate themselves to account for the 3 situations which occur when PRP is a 'Hit'.

I know this is a touch confusing, so please feel free to ask questions.
[pre]
data set1;
input Hit Z zn BSi$ PRP$;
datalines;
5 100 23 L Out
5 100 23 L Hit
5 100 23 L Out
5 100 23 L Hit
5 100 23 L Out
14 87.5 23 R Out
14 87.5 23 R Hit
14 87.5 23 R Hit
14 87.5 23 R Out
run;

data set2;
input zn BPT BBV DP$ BSi$ PRP$ fbp Percent;
datalines;
23 L Hit 3 .8
23 L Hit 4 .1
23 L Hit 9 .1

23 R Hit 3 .6
23 R Hit 4 .3
23 R Hit 9 .1
run;

data set3;

5 100 23 L Out . .

5 100 23 L Hit 3 .8
5 100 23 L Hit 4 .1
5 100 23 L Hit 9 .1

5 100 23 L Out . .

5 100 23 L Hit 3 .8
5 100 23 L Hit 4 .1
5 100 23 L Hit 9 .1

5 100 23 L Out . .

14 87.5 23 R Out . .

14 87.5 23 R Hit 3 .6
14 87.5 23 R Hit 4 .3
14 87.5 23 R Hit 9 .1

14 87.5 23 R Hit 3 .6
14 87.5 23 R Hit 4 .3
14 87.5 23 R Hit 9 .1

14 87.5 23 R Out . .
run;
[/pre]

Thanks,
C

Message was edited by: CharlesR Message was edited by: CharlesR

[Patrick]

Hi

Looks to me as if the data for set2 don't match the mapping in the input statement.

Anyway: Below example should point you into the right direction.

data set1;
input Hit Z zn BSi $ PRP $ ;
datalines;
5 100 23 L Out
5 100 23 L Hit
5 100 23 L Out
5 100 23 L Hit
5 100 23 L Out
14 87.5 23 R Out
14 87.5 23 R Hit
14 87.5 23 R Hit
14 87.5 23 R Out
;
run;

data set2;
input zn BSi $ PRP $ var1 var2 8.;
datalines;
23 L Hit 3 .8
23 L Hit 4 .1
23 L Hit 9 .1
23 R Hit 3 .6
23 R Hit 4 .3
23 R Hit 9 .1
;
run;


proc sql;
create table set3 as
select L.*, R.var1, R.var2
from set1 L left join set2 r
on L.zn=R.zn and L.BSi=R.BSi and L.PRP=R.PRP
;
quit;


HTH
Patrick