Many to Many match issue

[coug914]

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

Any suggestions? Thanks in advance!

[Linlin]

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

[Ksharp · Accepted solution]

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

[coug914]

Very helpful...thank you very much!