topic Many to Many match issue in SAS Procedures

Many to Many match issue

coug914 — Tue, 27 Dec 2011 02:00:44 GMT

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!

Re: Many to Many match issue

Linlin — Tue, 27 Dec 2011 04:19:28 GMT

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

Many to Many match issue

Ksharp — Tue, 27 Dec 2011 08:30:18 GMT

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

Re: Many to Many match issue

coug914 — Tue, 27 Dec 2011 15:47:02 GMT

Very helpful...thank you very much!