topic Re: Matching firms on industry and size in SAS Procedures

Matching firms on industry and size

trich12 — Mon, 01 Oct 2012 21:20:05 GMT

Hi. I have a dataset of firms where some firms are involved in mergers but most are not. I want to find five matches each for the acquiring and target firms based on industry (2 digit SIC) and closest in size (smallest absolute difference in sales) from the firms in the data that were not involved in a merger within the last 3 years of the actual merger.

My identifying variables are as follows: cnum = identifier for all firms

acquirer_cusip = identifier for acquiring firms

target_cusip = identifier for target firms

Acquirer_cusip = cnum when the firm is involved in a merger. Acquirer_cusip is blank if the firm was not involved in a merger. The same goes for target_cusip.
Here is an example of what the data looks like:

Fyear Cnum Acquirer_Cusip Target_Cusip SIC Sales

1992 123456 123456 444444 01 2000

1992 383838 . . 01 2400

1992 444444 123456 444444 03 700

1992 999999 . . 03 600

When the acquirer_cusip = cnum, I have data on the acquirer's SIC and sales. When the target_cusip = cnum, I have data on its SIC and sales. I also have the SIC and Sales on all the firms that are not involved in a merger. I created this dataset by merging a dataset on merger firms with Compustat firm data. I can work with the two datasets separately if I need to, but I thought since I already merged them I would work with this combined dataset.

I hope I made sense describing what I have and what I want to do, but please let me know if I should provide any other information on my data. I would really appreciate any help you could give me with finding matches for these firms. Thanks!

Re: Matching firms on industry and size

PGStats — Tue, 02 Oct 2012 01:02:26 GMT

It's not clear what you expect as a result, but I think this should get you closer...

data all;
input Fyear Cnum Acquirer_Cusip Target_Cusip SIC Sales;
datalines;
1992      123456         123456               444444         01       2000
1992      383838             .                         .              01       2400
1992      444444         123456               444444         03        700
1992      999999             .                         .              03        600
;

/* split the dataset in 3 parts */

data acquirer target other;
set all;
if Cnum = Acquirer_Cusip then output acquirer;
else if Cnum = Target_Cusip then output target;
else output other;
run;

/* Find the firms comparable to Acquirers */

proc sql;
create table likeAcquirer as
select O.*, A.Cnum as Anum, abs(O.sales-A.sales) as salesDiff
from other as O inner join acquirer as A
on O.fYear between A.fYear - 2 and A.fYear and O.sic=A.sic
where O.Cnum not in (select cNum from acquirer) and
O.Cnum not in (select cNum from target)
order by Anum;

/* Find the firms comparable to Targets */
create table likeTarget as
select O.*, T.Cnum as Tnum, abs(O.sales-T.sales) as salesDiff
from other as O inner join target as T
on O.fYear between T.fYear - 2 and T.fYear and O.sic=T.sic
where O.Cnum not in (select cNum from target) and
O.Cnum not in (select cNum from acquirer)
order by Tnum;
quit;

/* Find the 5 closest firms to Acquirers */

proc means data=likeAcquirer noprint;
by Anum;
output out=closeAcquirerW idgroup(min(salesDiff) out[5] (fYear Cnum)=);
run;

/* Find the 5 closest firms closest to Targets */

proc means data=likeTarget noprint;
by Tnum;
output out=closeTargetW idgroup(min(salesDiff) out[5] (fYear Cnum)=);
run;

/* List the firms sequentially */

proc transpose data=closeAcquirerW out=closeAcquirer prefix=A_;
by Anum; var Cnum:; run;

proc transpose data=closeTargetW out=closeTarget prefix=T_;
by Tnum; var Cnum:; run;

Re: Matching firms on industry and size

trich12 — Tue, 02 Oct 2012 02:23:11 GMT

Thank you so much! I will let you know how it goes when I run it, but it looks like it is exactly what I need. I really appreciate your help!

Re: Matching firms on industry and size

trich12 — Tue, 02 Oct 2012 23:11:45 GMT

I ran the code and it all worked perfectly with my data. I was hoping to get the five matches from only firms that have the same 2 digit SIC number. Is there a line I can insert into the *Find the 5 closest firms* section that will narrow it down to firms with the same 2 digit SIC, then chose the 5 firms with the smallest difference in sales from those?

Thank you again! I can't even tell you how much this has helped me.

Re: Matching firms on industry and size

PGStats — Wed, 03 Oct 2012 00:21:53 GMT

Glad I could help. A few minutes after I posted my original answer, I edited the SQL queries to add "and O.sic=A.sic" and "and O.sic=T.sic" which I thought would restrict the comparisons to firms of the same SIC number. Did you take the original version of my code or the edited version with added conditions?

Re: Matching firms on industry and size

trich12 — Mon, 15 Oct 2012 21:14:26 GMT

I had used the original version, but the edited version was perfect. Thanks! Sorry to ask you to walk me through all this, but I've tried to get this on my own and couldn't do it.

Now that I have one dataset with actual targets and their 5 closest matches, and another for actual acquirers and their closest matches, I want to merge those. I basically want 11 observations for every merger, where one is the actual acquirer and target, 5 are the close matches for the target paired with the actual acquirer, and 5 are the matches for the acquirer paired with the actual target. I have fewer targets than acquirers, so I'll need to make sure that I'm using only mergers where I have matches for both the actual target and actual acquirer.

I hope this makes sense. I can't wrap my head around how to merge these datasets of matches with the actual mergers. I will really appreciate any help you can give!

Re: Matching firms on industry and size

trich12 — Fri, 08 Feb 2013 16:14:40 GMT

Hi PG. I was just re-running this code and I'm getting several duplicate cnum matches. Do you know of any way to add into the code that I want 5 unique cnum matches? Thanks!

Re: Matching firms on industry and size

PGStats — Fri, 08 Feb 2013 17:51:36 GMT

Try these small modifs :

data acquirer target other;
set all;
if Cnum = Acquirer_Cusip then output acquirer;
else if Cnum = Target_Cusip then output target;
else output other;
run;

proc sql;
create table likeAcquirer as
select O.Cnum, O.fYear, A.Cnum as Anum, min(abs(O.sales-A.sales)) as salesDiff
from other as O inner join acquirer as A
on O.fYear between A.fYear - 2 and A.fYear and O.sic=A.sic
where O.Cnum not in (select cNum from acquirer) and
O.Cnum not in (select cNum from target)
group by Anum, O.Cnum, O.fYear;

create table likeTarget as
select O.Cnum, O.fYear, T.Cnum as Tnum, min(abs(O.sales-T.sales)) as salesDiff
from other as O inner join target as T
on O.fYear between T.fYear - 2 and T.fYear and O.sic=T.sic
where O.Cnum not in (select cNum from target) and
O.Cnum not in (select cNum from acquirer)
group by Tnum, O.Cnum, O.fYear;
quit;

proc means data=likeAcquirer noprint;
by Anum;
output out=closeAcquirerW idgroup(min(salesDiff) out[5] (fYear Cnum)=);
run;

proc means data=likeTarget noprint;
by Tnum;
output out=closeTargetW idgroup(min(salesDiff) out[5] (fYear Cnum)=);
run;

data closeAcquirer(keep=order Anum Cnum fYear);
set closeAcquirerW;
array C{5} Cnum_:; array Y{5} fYear_:;
do order = 1 to 5;
Cnum = C{order}; fYear = Y{order}; output;
end;
run;

data closeTarget(keep=order Tnum Cnum fYear);
set closeTargetW;
array C{5} Cnum_:; array Y{5} fYear_:;
do order = 1 to 5;
Cnum = C{order}; fYear = Y{order}; output;
end;
run;