DATA Step, Macro, Functions and more

Variable creation

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Variable creation

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

 

 

 

 

 

 

 


Interlocking.PNG

Accepted Solutions
Solution
‎10-06-2015 10:36 AM
Respected Advisor
Posts: 4,919

Re: Variable creation

Posted in reply to factorhedge

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;
PG

View solution in original post


All Replies
Community Manager
Posts: 291

Re: Variable creation

Posted in reply to factorhedge

Thanks for your question.

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

Best,

Lainie

Solution
‎10-06-2015 10:36 AM
Respected Advisor
Posts: 4,919

Re: Variable creation

Posted in reply to factorhedge

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;
PG
Contributor
Posts: 24

Re: Variable creation

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 211 views
  • 0 likes
  • 3 in conversation