Calculating the total number of id by year over the last three years

[nazmul]

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;

[art297]

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

[nazmul]

Thank you for your response sir. But the want file does not match the want file I put in my question. Want file should have the same number of observation as the have file. Please check my want file for reference. I really appreciate your effort to help me Sir.

[Patrick · Accepted solution]

@nazmul

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;

[nazmul]

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.