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
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 |
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 |
Or
data FLAGS;
set SORTED;
by CUSIP CASH STOCK MIXED ;
if ^first.MIXED & lag(FYEAR) - FYEAR < 3 then delete;
run;
Thank you ChrisNZ for being helpful.
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.