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!
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
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
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
Very helpful...thank you very much!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.