topic Re: Variable creation in SAS Programming

Variable creation

factorhedge — Wed, 30 Sep 2015 00:17:25 GMT

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

Re: Variable creation

LainieH — Wed, 30 Sep 2015 01:38:27 GMT

Thanks for your question.

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

Best,

Lainie

Re: Variable creation

PGStats — Wed, 30 Sep 2015 02:57:40 GMT

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;

Re: Variable creation

factorhedge — Tue, 06 Oct 2015 00:33:55 GMT

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