
/* Program retrieves crime data for other executives */

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

* Read the data ;

data positiot;
set lib100.positions;

keep id firm 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 year;

run;

data positions;
set positiot positiot_a;
run;

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

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

proc transpose data = positions out = positions_2 prefix = id;
by firm year;
var id;

run;

data positions_3;
merge positions positions_2;
by firm year;
run;

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

data positions_4;
set positions_3;

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

drop _name_;

run;

* Family members;

data family_data;
set lib100.kaikki_perheet_data_250925;

keep id mukana m_id f_id;

run;


data positions_5;
merge positions_4 family_data;
by id;

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

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

if m_id = '' and f_id = '' then delete;

drop m_id f_id mukana;

run;

proc transpose data = positions_5 out = positions_6;
by id firm year;
var id1 -- id30;

run;

data positions_7;
set positions_6;
if col1 = '' then delete;
drop _name_;
rename col1 = Board_ID;
run;

data Convictions_board;
 set lib100.convictions_kp_tekovuosi_300924;   
 
if year > 1993;

keep id year D_muu_rikos_4 D_talous_kaikki;

rename id = Board_id;

run;

* Adds crime data to the data on CEO and boardmembers;

proc sort data = positions_7;
by Board_id year;
run;

proc sort data = Convictions_board;
by Board_id year;
run;

data positions_8;
merge positions_7 Convictions_board;
by board_id year;

if id = '' then delete;

if D_Muu_rikos_4 = . then D_Muu_rikos_4 = 0;
if D_Talous_kaikki = . then D_Talous_kaikki = 0;

run;

proc sort data = positions_8;
by id firm year;
run;

proc means noprint data = positions_8;
by id firm year;
var D_Muu_rikos_4 D_Talous_kaikki;

output out = Board_crimes mean = Mean_D_Muu_rikos_4 Mean_D_Talous_kaikki;

run;

data positions_9;
merge positions_8 Board_crimes;
by id firm year;

D_Mean_D_muu_rikos_4 = 0;
if Mean_D_Muu_rikos_4 > 0 then D_Mean_D_muu_rikos_4 = 1;

D_Mean_D_Talous_kaikki = 0;
if Mean_D_Talous_kaikki > 0 then D_Mean_D_Talous_kaikki = 1;

drop board_id _type_ _freq_ D_Muu_rikos_4 D_Talous_kaikki ;

run;

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

* Saves data;

data lib100.Board_and_CEO_crimes_260925;
set positions_9;

run;



