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

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

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

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

 


Accepted Solutions
Solution
‎07-11-2017 11:44 PM
PROC Star
Posts: 1,760

Re: Dropping companies which engaged in another bid in the previous 3 years with the same payment me

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

 

View solution in original post


All Replies
Solution
‎07-11-2017 11:44 PM
PROC Star
Posts: 1,760

Re: Dropping companies which engaged in another bid in the previous 3 years with the same payment me

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

 

PROC Star
Posts: 1,760

Re: Dropping companies which engaged in another bid in the previous 3 years with the same payment me

Or


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

Re: Dropping companies which engaged in another bid in the previous 3 years with the same payment me

Thank you 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 113 views
  • 1 like
  • 2 in conversation