data work.have;
attrib Company_A length = $50
Company_B length = $50
Year format = best4.
Deal_Type length = $50
;
infile datalines dlm = ",";
input Company_A $ Company_B $ Year Deal_Type $;
datalines;
P&G,Pepsi,2006,Partnership
Coke,Pepsi,2007,Partnership
Pringles,P&G,2007,Partnership
Pringles,P&G,2005,Partnership
Pringles,P&G,2008,M&A
;
run;
data work.have;
ID = _N_;
set work.have;
run;
proc sql;
create table work.want as
select a.*
, sum(b.ID is not missing) as NumOfCoop
, sum(b.ID is not missing
and b.deal_type eq "Partnership"
and ( ( a.company_a = b.company_a and a.company_b = b.company_b )
or ( a.company_b = b.company_a and a.company_a = b.company_b ) )
) as NumOfCoopsCompACompB
from work.have as a
left join work.have as b on a.year ge b.year
and a.year - b.year le 5
and (a.company_b = b.company_b or a.company_b = b.company_a)
group by a.ID, a.company_a, a.company_b, a.year, a.deal_type
;
quit;
... View more
