
/* Program identifies family executives */

libname lib100 "W:\Tutkimusprojektit\Kirjanpito\Dokumentaatio\JAR\Datat\";
run;

data families;
set lib100.kaikki_perheet_data_250925;
run;

proc sort data = families;
by id;
run;

* Reads position data;

data positiot;
set lib100.positions;

if year > 1993;

keep id firm asema year;

run;

data positiot_a;
set lib100.positions_asiakastieto_070824;

if 2016 <= year <= 2019;
if asema = 'Varsinainen jäsen' or asema = 'Puheenjohtaja' or asema = 'Toimitusjohtaja';

if asema = 'Varsinainen jäsen' then asema = 'J';
if asema = 'Puheenjohtaja' then asema = 'PJ';
if asema = 'Toimitusjohtaja' then asema = 'TJ';

keep id firm asema year;

run;

data positions;
set positiot positiot_a;
run;

proc sort nodupkey data = positions;
by id firm year;
run;

data positions_2;
merge positions families;
by id;

if FIRM = '' then delete;
if id = '' then delete;
if mukana = 1;

drop asema;

run;

data positiot_family;
set lib100.positions;

if year > 1993;

keep id firm asema year;

run;

data positiot_family_a;
set lib100.positions_asiakastieto_070824;

if 2016 <= year <= 2019;
if asema = 'Varsinainen jäsen' or asema = 'Puheenjohtaja' or asema = 'Toimitusjohtaja';


if asema = 'Varsinainen jäsen' then asema = 'J';
if asema = 'Puheenjohtaja' then asema = 'PJ';
if asema = 'Toimitusjohtaja' then asema = 'TJ';

keep id firm asema year;

run;

data family_positions;
set positiot_family positiot_family_a;

keep id year firm asema;

run;

proc sort nodupkey data = family_positions;
by firm year id;
run;

proc sort data = family_positions;
by firm year;
run;

proc transpose data = family_positions out = family_positions_2 prefix = id;
by firm year;
var id;

run;

* Combine positions datas;

proc sort nodupkey data = family_positions_2;
by firm year;
run;

proc sort nodupkey data = positions_2;
by firm year id;
run;

data Person_and_family_positions;
merge positions_2 family_positions_2;
by firm year;

if id = '' then delete;

run;

* Add position data on CEOs;

data ceos_p;
set lib100.ceos;

if year > 1993;

keep firm id year;

rename id = CEO_ID;

run;

data ceos_a;
set lib100.asiakastieto_ceos_080225;

if 2016 <= year <= 2019;

if asema = 'Puheenjohtaja' then asema = 'PJ';
if asema = 'Toimitusjohtaja' then asema = 'TJ';

keep firm id year;

rename id = CEO_ID;

run;

data CEOs;
set ceos_p ceos_a;

run;

proc sort nodupkey data = CEOs;
by firm year;
run;

data Person_and_family_positions_2;
merge Person_and_family_positions CEOs;
by firm year;

if id = '' then delete;

run; 

* Check if any familymember is a board member or a CEO;

data Person_and_family_positions_3;
set Person_and_family_positions_2;

familymember_on_board = 0;

if m_id = id1 or m_id = id2 or m_id = id3 or m_id = id4 or m_id = id5 or m_id = id6 or m_id = id7 or m_id = id8 or m_id = id9 or 
m_id = id10 or m_id = id11 or m_id = id12 or m_id = id13 or m_id = id14 or m_id = id15 or m_id = id16 or m_id = id17 or m_id = id18 or 
m_id = id19 or m_id = id20 or m_id = id21 or m_id = id22 or m_id = id23 or m_id = id24 or m_id = id25 or m_id = id26 or m_id = id27
or m_id = id28 or m_id = id29 or m_id = id30 then familymember_on_board = 1;

if f_id = id1 or f_id = id2 or f_id = id3 or f_id = id4 or f_id = id5 or f_id = id6 or f_id = id7 or f_id = id8 or f_id = id9 or 
f_id = id10 or f_id = id11 or f_id = id12 or f_id = id13 or f_id = id14 or f_id = id15 or f_id = id16 or f_id = id17 or f_id = id18 or 
f_id = id19 or f_id = id20 or f_id = id21 or f_id = id22 or f_id = id23 or f_id = id24 or f_id = id25 or f_id = id26 or f_id = id27
or f_id = id28 or f_id = id29 or f_id = id30 then familymember_on_board = 1;

familymember_CEO = 0;

if m_id = CEO_ID or f_id = CEO_ID then familymember_CEO = 1;

run;


* Saves data;

data lib100.Familymember_on_board_220925;
set Person_and_family_positions_3;

keep id firm year familymember_on_board familymember_CEO;

run;
