Variable creation

[factorhedge]

Hi I was wondering if anyone could help me to create the variable(shown below) for my project

I have the dataset as shown in my attachment , it is not orginal data but the variables are similar

Variables 

CEO_Ind = 1 if the Director_name is CEO , 0 otherwise 

Ind_indicator = 1 if Director_name is independent , 0 otherwise 

These two indicator variables are mutually exclusive for e.g. if CEO_Ind = 1 then by definition Ind_indicator = 0. However there could be non independent and non CEO i.e both the indicators will have 0

Variable I need is 

For Firm_code 11 if the Director_name A also appears in any other company for instance Firm_code 265 along with any of other Directors that also appear in Firm_code 11 "within the same year" then Interlocking = 1 else Interlocking = 0 

For instance as per the attached spreadsheet 

For year = 2006 

For FIRM_CODE 11 , Director A is accompanied by Directors B and C in 2006 and Directors A and C also appear for FIRM_CODE 265 so the interlocking variable for FIRM_CODE 11 will be 1 , 0 otherwise

Similarly for year = 2007 

For FIRM_CODE 11 , Director A is accompanied by Directors B and C in 2007 and Directors A and B also appear for FIRM_CODE 265 so the interlocking variable for FIRM_CODE 11 will be 1 , 0 otherwise

[LainieH]

Thanks for your question.

I've moved your question to this topical forum as more experts will be able to help here.

Best,

Lainie

[PGStats · Accepted solution]

Please post example data as text, next time. I changed your data slightly so that year=2007 is NOT interlocked

data have;
input firm_code year director$;
datalines;
11 2006 A
11 2006 B
11 2006 C
11 2007 A
11 2007 B
11 2007 C
11 2008 A
11 2008 B
11 2008 C
11 2008 D
265 2006 X
265 2006 A
265 2006 C
265 2007 X
265 2007 A
265 2007 D
265 2008 X
265 2008 A
265 2008 B
265 2008 C
265 2009 D
;

proc sql;
create table interlocks as
select year, firm_code_a, firm_code_b
from (
    select a.year, a.firm_code as firm_code_a, b.firm_code as firm_code_b
    from have as a inner join have as b on
        a.year=b.year and 
        a.director=b.director and 
        a.firm_code ne b.firm_code
)
group by year, firm_code_a, firm_code_b
having count(*) > 1;
create table want as
select a.*, b.year is not missing as interlock
from 
    (select distinct year, firm_code from have) as a left join 
    interlocks as b on 
        a.year=b.year and 
        a.firm_code=b.firm_code_a;
select * from want;
quit;

[factorhedge]

Thanks a lot for your reply. I will implement on my entire dataset.