Dropping companies which engaged in another bid in the previous 3 years with the same payment method

[nazmul]

Dear Everyone,

I have a dataset which lists all the successful mergers. I just put a sample dataset for your use in this post. In my research I have to keep those acquirers which  have not engaged in another bid in the previous 3 years using the same merger consideration. Here, consideration indicates the payment method for the merger. This ensures no firm with the same merger consideration appears more than once in our portfolios at any point in time. For example, company 123 had paid with only cash for both of the mergers in 1994 and 1995. So, I want to drop company 123 ih the financial year 1995. Company 123 had another merger in 1994 with mixed payment and the company had no other mergers in the previous 3 years for which it paid with the mixture of cash and stock, so I will keep company 123 with mixed payment in the year 1994.

The same company 123 had another merger in 1996 for which it paid with only stock. The company had no other mergers in the previous 3 years for which it paid with only stock. So I will keep company 123 with stock payment in the financial year 1996. I have included my expected results in this post.

Could anyone please give me the right SAS code to get my expected results? I will appreciate your help.

In the following dataset:

cusip=Company id;

fyear=financial year;

cash=cash consideration only; (merger payment by cash)

stock=stock consideration only; (merger payment by stock)

mixed=both cash and stock consideration; (merger payment by cash and stock)

My Dataset:

cusip fyear cash stock mixed
123 1994 1 0 0

123 1994 0 0 1
123 1995 1 0 0
123 1996 0 1 0
123 1997 0 0 1
123 1998 0 0 1
123 1999 0 1 0
124 1996 1 0 0
124 1997 0 1 0
124 1998 0 0 1

Expected Output:

cusip fyear cash stock mixed

123 1994 0 0 1
123 1995 1 0 0
123 1996 0 1 0
123 1998 0 0 1
123 1999 0 1 0
124 1996 1 0 0
124 1997 0 1 0
124 1998 0 0 1

[ChrisNZ · Accepted solution]

Like this?

proc sort data=HAVE out=SORTED; 
  by CUSIP CASH STOCK MIXED descending FYEAR;
run;
data FLAGS;
  set SORTED;
  if CUSIP=lag(CUSIP) 
   & CASH =lag(CASH) 
   & STOCK=lag(STOCK)  
   & MIXED=lag(MIXED) 
   & lag(FYEAR) -FYEAR < 3 then delete;
run;
proc sort data=FLAGS out=WANT; 
  by CUSIP  FYEAR;
run;

CUSIP	FYEAR	CASH	STOCK	MIXED
123	1994	0	0	1
123	1995	1	0	0
123	1996	0	1	0
123	1998	0	0	1
123	1999	0	1	0
124	1996	1	0	0
124	1997	0	1	0
124	1998	0	0	1

[ChrisNZ]

Or

data FLAGS;
  set SORTED;
  by CUSIP CASH STOCK MIXED ;
  if ^first.MIXED & lag(FYEAR) - FYEAR < 3 then delete;
run;

[nazmul]

Thank you ChrisNZ for being helpful.