Counting number of deals prior to the focal year

[jimmychoi]

Hi, this is my dataset

data work.deals;
attrib TargetFirm length = $50
		Acquirer length = $50
		Year format = best4.
		Deal_Type length = $50
		;
infile datalines dlm = ",";
input Year Deal_Type $ TargetFirm $ Acquirer $;
datalines;
2019,M&A,Foxcon,Apple
2018,Partnership,Apple,Samsung
2016,Partnership,Apple,Huawei
2015,Partnership,Apple,LG
2015,Partnership,Foxcon,Apple
2015,M&A,BOSCH,Apple
2015,M&A,Apple,Blackberry
2013,Partnership,Foxcon	Apple
;
run;

I'm trying to do two things:

1. Acq_NumOfCoop

Count the number of deals (including partnership, M&A, and etc) of last 5 years prior to the deal (M&A) year

ex) Let's say there's Apple and there was a M&A with Foxcon in year 2019 (focal year).

Then, the question is, how many deals Apple accomplished, regardless of the subject (company) of the deal,  from the year 2014 to 2018?

2. AcqTar_NumOfCoop

Count the number of partnerships (this doesn't count M&A deals) between specific companies, from last 5 years prior to a M&A deal.

ex) If there was a M&A deal between Apple and Foxcon in year 2019, then before this, how many partnership deals were made between those two specific companies, from 2014 to 2018?

Expected outcome would be like this:

year	Deal.Type	TargetFirm	Acquirer	Acq_NumOfCoop	AcqTar_NumOfCoop
2019	M&A	Foxcon	Apple	2	1
2018	Partnership	Apple	Samsung	N/A	N/A
2016	Partnership	Apple	Huawei	N/A	N/A
2015	Partnership	Apple	LG	N/A	N/A
2015	Partnership	Foxcon	Apple	N/A	N/A
2015	M&A	BOSCH	Apple	1	0
2015	M&A	Apple	Blackberry	0	0
2013	Partnership	Foxcon	Apple	N/A	N/A

Please help.

[novinosrin · Accepted solution]

Hi @jimmychoi 

data work.deals;
attrib TargetFirm length = $50
		Acquirer length = $50
		Year format = best4.
		Deal_Type length = $50
		;
infile datalines dlm = "," truncover;
input Year Deal_Type $ TargetFirm $ Acquirer $;
datalines;
2019,M&A,Foxcon,Apple
2018,Partnership,Apple,Samsung
2016,Partnership,Apple,Huawei
2015,Partnership,Apple,LG
2015,Partnership,Foxcon,Apple
2015,M&A,BOSCH,Apple
2015,M&A,Apple,Blackberry
2013,Partnership,Foxcon,Apple
;
run;

data want;
if _n_=1 then do;
if 0 then set deals(rename=(year=_year deal_type=_deal_type));
  dcl hash H (dataset:'deals(rename=(year=_year deal_type=_deal_type))',multidata:'y') ;
   h.definekey  ("Acquirer") ;
   h.definedata ("_year","_deal_type") ;
   h.definedone () ;
 end;
 set deals;
 if deal_type='M&A' then do;
 Acq_NumOfCoop=0;
 AcqTar_NumOfCoop=0;
 do rc=h.find() by 0 while(rc=0);
if  year-5<=_year<=year-1 then Acq_NumOfCoop=sum(Acq_NumOfCoop,1);
if  year-5<=_year<=year-1 and _deal_type ne 'M&A' then AcqTar_NumOfCoop=sum(AcqTar_NumOfCoop,1);
 rc=h.find_next();
 end;
 end;
 drop _: rc;
 run;

I appreciate your well written question this time 🙂 thumbs up !!!

[novinosrin]

This one is rather Tedious but whatever!!!

data work.deals;
attrib TargetFirm length = $50
		Acquirer length = $50
		Year format = best4.
		Deal_Type length = $50
		;
infile datalines dlm = "," truncover;
input Year Deal_Type $ TargetFirm $ Acquirer $;
datalines;
2019,M&A,Foxcon,Apple
2018,Partnership,Apple,Samsung
2016,Partnership,Apple,Huawei
2015,Partnership,Apple,LG
2015,Partnership,Foxcon,Apple
2015,M&A,BOSCH,Apple
2015,M&A,Apple,Blackberry
2013,Partnership,Foxcon,Apple
;
run;


proc sql;
create table want as
select a.*,ifn(a.deal_type='M&A',sum(a.year-5<=b.year<=a.year-1),.) as Acq_NumOfCoop,ifn(a.deal_type='M&A',sum(a.year-5<=b.year<=a.year-1 and b.deal_type ne 'M&A'),.) as AcqTar_NumOfCoop
from deals a, deals b
where a.Acquirer=b.Acquirer 
group by a.Acquirer,a.year,a.targetfirm,a.deal_type
order a.year desc;
quit;