Hello everyone;
I am calculating the experience of acquirers in mergers and acquisitions by counting the number of mergers each acquirors completed in the last 3 years. Year indicates the year of merger completion, CUSIP is the company ID (each company has a unique cusip ID). An acquiror may announce multiple mergers in a single year. So CUSIP may show up more than once in a single year in the have file. By counting CUSIP ID over the last 3 years should give me the measure of acquiror's experience. Frequency_last3yrs is the measure of acquiror's experience. Say CUSIP 52XC23 completed its first mergers in 1994. So the experience of the acquiror in that year is 1. The company completed two more mergers in 1996. So the experience measure will count the mergers of the company in the last three years-1994, 1995 and 1996. The experience is 3 because the company has one merger in 1994 and 2 in 1996. Please help me in measuring the variable in SAS. I will appreciate your help.
I think one solution could be that I can calculate the frequency of each unique cusip by year (This will help me to put the right frequency for more than one completion in the same year), and then calculate the total number of mergers in the last three years and merge the output file with original have file to put the measure of experience with each cusip. But I do not know the right code to do it.
data have;
input year CUSIP $;
datalines;
1980 00034x
1980 005690
1981 56295A
1994 52XC23
1996 52XC23
1996 52XC23
1996 56295A
1997 56295A
1998 56295A
1998 005690
2001 56295A
run;
data want;
input year CUSIP $ Frequency_last3yrs;
datalines;
1980 00034x 1
1980 005690 1
1981 56295A 1
1994 52XC23 1
1996 52XC23 3
1996 52XC23 3
1996 56295A 1
1997 56295A 2
1998 56295A 3
1998 005690 1
2001 56295A 1
run;
The following creates the data as per your Want table.
data have;
input year CUSIP $;
datalines;
1980 00034x
1980 005690
1981 56295A
1994 52XC23
1996 52XC23
1996 52XC23
1996 56295A
1997 56295A
1998 56295A
1998 005690
2001 56295A
run;
data want;
if _n_=1 then
do;
dcl hash h1(dataset:'have', multidata:'y');
h1.defineKey('year','cusip');
h1.defineDone();
end;
set have;
Frequency_last3yrs=0;
do _year=year-2 to year;
do while(h1.do_over(key:_year,key:cusip) eq 0);
Frequency_last3yrs+1;
end;
end;
drop _year;
run;
Or as a SQL
proc sql;
create table want2 as
select
year,
cusip,
(select count(*) from have i where i.cusip=o.cusip and i.year between o.year-2 and o.year) as count
from have o
;
quit;
I think you are just trying to do something like:
data have; input year CUSIP $; datalines; 1980 00034x 1980 005690 1981 56295A 1994 52XC23 1996 52XC23 1996 52XC23 1996 56295A 1997 56295A 1998 56295A 1998 005690 2001 56295A run; proc sort data=have out=want; by cusip year; run; data want; do until (last.cusip); set want; by cusip; if last.cusip then _endyear=year; end; do until (last.cusip); set want; by cusip; if first.cusip then Frequency_last3yrs=0; if year ge _endyear-2 then do; Frequency_last3yrs+1; output; end; end; run;
Art, CEO, AnalystFinder.com
The following creates the data as per your Want table.
data have;
input year CUSIP $;
datalines;
1980 00034x
1980 005690
1981 56295A
1994 52XC23
1996 52XC23
1996 52XC23
1996 56295A
1997 56295A
1998 56295A
1998 005690
2001 56295A
run;
data want;
if _n_=1 then
do;
dcl hash h1(dataset:'have', multidata:'y');
h1.defineKey('year','cusip');
h1.defineDone();
end;
set have;
Frequency_last3yrs=0;
do _year=year-2 to year;
do while(h1.do_over(key:_year,key:cusip) eq 0);
Frequency_last3yrs+1;
end;
end;
drop _year;
run;
Or as a SQL
proc sql;
create table want2 as
select
year,
cusip,
(select count(*) from have i where i.cusip=o.cusip and i.year between o.year-2 and o.year) as count
from have o
;
quit;
Thank you Patrick. I really appreciate your help. You gave me the desired code. I especially like your proc sql code because it was easy to understand for me.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.