Conditional match test firms with control firms

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Conditional match test firms with control firms

Hi all,

 

Would greatly appreciate some help with modifying a code for matching.

I have two datasets, one with treatment firms (presmall) and one with a list of non-event firms (presmall) to obtain controls from.

I found and modified the code below from the forum (thanks!) but I need to modify it further by setting some conditional restrictions.

 

Here is the intended matching procedure:

1. Match by the 2-digit industry code (twosic), then pick a control that has the most similar market value of assets (mva) and market to book ratio (mb) to the treatment firm but not exceeding by more than 10%. If no match satifisfies the criteria, then;

 

2. Match by the 1-digit industry code (onesic), then pick a control that has the most similar mva and mb to the treatment firm but not exceeding by more than 10%. If no match satifies the criteria, then;

 

3. Match without any industry restriction, then pick a control that has the most similar mva and mb to the treatment firm but not exceeding by more than 10%.

 

The first set of code below creates an id for the non-event firm dataset. The second set of code below finds the match and indicates the id of the matched firm under the 'control' column of the treatment firm dataset.

 

data match.presmallc;
set match.presmallc;
id = _n_;
run;

 

proc sql;
create table match.presmallmatch(drop=diff) as
select a.*, b.id as control,
log10(a.mva/b.mva)**2 + 4*(a.mb-b.mb)**2 as diff
from match.presmall a, match.presmallc b
where a.date=b.date and a.twosic=b.twosic
group by a.cusip6
having diff=min(diff);
quit;


Accepted Solutions
Solution
‎12-18-2015 03:02 PM
Respected Advisor
Posts: 4,811

Re: Conditional match test firms with control firms

You could do it with left joins :

data a;
input id sic2 sic1 mva mb;
datalines;
1 11 1 10 .10
2 21 2 20 .20
3 31 3 30 .30
;

data b;
input id sic2 sic1 mva mb;
datalines;
1 11 1 10 .11
2 11 1 12 .12
3 22 2 40 .40
4 23 2 21 .21
5 24 2 22 .22
6 51 5 31 .31
7 61 6 32 .32
;

%let maxd=0.1;
proc sql;
select
    a.*,
    coalesce(b1.id, b2.id, b3.id) as b_id,
    coalesce(b1.sic2, b2.sic2, b3.sic2) as b_sic2,
    coalesce(b1.sic1, b2.sic1, b3.sic1) as b_sic1,
    coalesce(b1.mva, b2.mva, b3.mva) as b_mva,
    coalesce(b1.mb, b2.mb, b3.mb) as b_mb,
    log10(a.mva/calculated b_mva)**2 + 4*(a.mb-calculated b_mb)**2 as dist
from
    a left join 
    b as b1 on a.sic2=b1.sic2 and log10(a.mva/b1.mva)**2 + 4*(a.mb-b1.mb)**2 < &maxd left join
    b as b2 on b1.sic2 is missing and a.sic1=b2.sic1 and log10(a.mva/b2.mva)**2 + 4*(a.mb-b2.mb)**2 < &maxd left join
    b as b3 on b1.sic2 is missing and b2.sic1 is missing and log10(a.mva/b3.mva)**2 + 4*(a.mb-b3.mb)**2 < &maxd
group by a.id
having dist = min(dist);
quit;
PG

View solution in original post


All Replies
Solution
‎12-18-2015 03:02 PM
Respected Advisor
Posts: 4,811

Re: Conditional match test firms with control firms

You could do it with left joins :

data a;
input id sic2 sic1 mva mb;
datalines;
1 11 1 10 .10
2 21 2 20 .20
3 31 3 30 .30
;

data b;
input id sic2 sic1 mva mb;
datalines;
1 11 1 10 .11
2 11 1 12 .12
3 22 2 40 .40
4 23 2 21 .21
5 24 2 22 .22
6 51 5 31 .31
7 61 6 32 .32
;

%let maxd=0.1;
proc sql;
select
    a.*,
    coalesce(b1.id, b2.id, b3.id) as b_id,
    coalesce(b1.sic2, b2.sic2, b3.sic2) as b_sic2,
    coalesce(b1.sic1, b2.sic1, b3.sic1) as b_sic1,
    coalesce(b1.mva, b2.mva, b3.mva) as b_mva,
    coalesce(b1.mb, b2.mb, b3.mb) as b_mb,
    log10(a.mva/calculated b_mva)**2 + 4*(a.mb-calculated b_mb)**2 as dist
from
    a left join 
    b as b1 on a.sic2=b1.sic2 and log10(a.mva/b1.mva)**2 + 4*(a.mb-b1.mb)**2 < &maxd left join
    b as b2 on b1.sic2 is missing and a.sic1=b2.sic1 and log10(a.mva/b2.mva)**2 + 4*(a.mb-b2.mb)**2 < &maxd left join
    b as b3 on b1.sic2 is missing and b2.sic1 is missing and log10(a.mva/b3.mva)**2 + 4*(a.mb-b3.mb)**2 < &maxd
group by a.id
having dist = min(dist);
quit;
PG
🔒 This topic is solved and locked.

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

Discussion stats
  • 1 reply
  • 4744 views
  • 0 likes
  • 2 in conversation