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
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;
Thanks for your question.
I've moved your question to this topical forum as more experts will be able to help here.
Best,
Lainie
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;
Thanks a lot for your reply. I will implement on my entire dataset.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.