Help using Base SAS procedures

Many to Many match issue

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

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

Any suggestions? Thanks in advance!


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

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

View solution in original post


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: 9,681

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

Re: Many to Many match issue

Very helpful...thank you very much!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 163 views
  • 3 likes
  • 3 in conversation