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.
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 !!!
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 !!!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.