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,651

Re: Variable creation

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

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,651

Re: Variable creation

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.

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

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