Solved
Contributor
Posts: 23

# Many to Many match issue

I'm trying to merge company data with investor data. For example, say I have three companies (data a) and two investors (data b). Each investor, however, does not necessarily hold every company. So, for instance, the first investor holds companies A and B and the second investor holds only company B. I want the merged dataset to have six observations - each investor's position in each company...even if that position is zero (i.e., missing). It seems like this is something I could do in proc SQL but I have not been able to figure it out. (Note my actual datasets are very large.)

For example,

Company market_weight

A               0.5

B               0.3

C               0.2

And

Investor Company investor_weight

1          A               0.4

1          B               0.6

2          B               1

And here is want I want for the "merged data"

Investor          company          investor_weight          market_weight

1                    A                    0.4                              0.5

1                    B                    0.6                              0.3

1                    C                    0.0                              0.2

2                    A                    0.0                              0.5

2                    B                    1.0                              0.3

2                    C                    0.0                              0.2

Accepted Solutions
Solution
‎12-27-2011 03:30 AM
Super User
Posts: 10,784

## Many to Many match issue

```data one;
input Company \$ market_weight ;
datalines;
A               0.5
B               0.3
C               0.2
;
run;
data two;
input Investor Company \$ investor_weight ;
datalines;
1          A               0.4
1          B               0.6
2          B               1
;
run;
proc sql ;
create table temp as
select *
from (select distinct investor from two),(select * from one);

create table want as
select temp.*,coalesce(investor_weight,0) as investor_weight
from temp left join two on two.investor=temp.investor and two.company=temp.company;
quit;

```

Ksharp

All Replies
Super Contributor
Posts: 1,636

## Re: Many to Many match issue

data a;

input Company \$ market_weight;

cards;

A               0.5

B               0.3

C               0.2

;

data b;

input Investor Company \$ investor_weight;

cards;

1          A               0.4

1          B               0.6

2          B               1

3           A              .2

3           B               .3

3           C               .5

;

run;

proc sort data=b;

by investor company;

proc transpose data=b out=look1(drop=_name_);

by investor;

id company;

run;

data look1;

set look1;

if a=. then a=0;

if b=. then b=0;

if c=. then c=0;

run;

proc transpose data=look1 out=look2(rename=(_name_=company col1=investor_weight));

by investor;

run;

proc sql;

create table want as

select Investor, look2.Company, investor_weight,market_weight

from look2,a

where look2.company=a.company;

quit;

proc print;run;

Linlin

Solution
‎12-27-2011 03:30 AM
Super User
Posts: 10,784

## Many to Many match issue

```data one;
input Company \$ market_weight ;
datalines;
A               0.5
B               0.3
C               0.2
;
run;
data two;
input Investor Company \$ investor_weight ;
datalines;
1          A               0.4
1          B               0.6
2          B               1
;
run;
proc sql ;
create table temp as
select *
from (select distinct investor from two),(select * from one);

create table want as
select temp.*,coalesce(investor_weight,0) as investor_weight
from temp left join two on two.investor=temp.investor and two.company=temp.company;
quit;

```

Ksharp

Contributor
Posts: 23