/* Program reads different datasets and merges them into one dataset for analyses*/

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

* Financial statement data;

data acc; 
set lib100.tilinpaatokset_kaikki_080622;

lag_TASE = lag(TASEX);

if FIRM ne lag(FIRM) or YEAR ^= lag(YEAR) + 1 then lag_TASE = .;

drop tase;

rename TASEX = TASE;

run;

data acc_2;
 set acc;
	
 if TPLV >= 100000 and TASE >= 100000;		* Requires sales and assets to be greater than 100 000 EUR;

 if OMAVAR > 100 then delete;				* Removes likely data errors;
 if opo < -150 or opo > 150 then delete;		

 if year > 1993;

* Inflation adjustment;

if year = 1994 then TASE2 = TASE * 1.43;
if year = 1995 then TASE2 = TASE * 1.42;
if year = 1996 then TASE2 = TASE * 1.41;
if year = 1997 then TASE2 = TASE * 1.39;
if year = 1998 then TASE2 = TASE * 1.37;
if year = 1999 then TASE2 = TASE * 1.36;
if year = 2000 then TASE2 = TASE * 1.31;
if year = 2001 then TASE2 = TASE * 1.28;
if year = 2002 then TASE2 = TASE * 1.26;
if year = 2003 then TASE2 = TASE * 1.25;
if year = 2004 then TASE2 = TASE * 1.25;
if year = 2005 then TASE2 = TASE * 1.23;
if year = 2006 then TASE2 = TASE * 1.21;
if year = 2007 then TASE2 = TASE * 1.18;
if year = 2008 then TASE2 = TASE * 1.14;
if year = 2009 then TASE2 = TASE * 1.14;
if year = 2010 then TASE2 = TASE * 1.12;
if year = 2011 then TASE2 = TASE * 1.09;
if year = 2012 then TASE2 = TASE * 1.06;
if year = 2013 then TASE2 = TASE * 1.04;
if year = 2014 then TASE2 = TASE * 1.03;
if year = 2015 then TASE2 = TASE * 1.03;
if year = 2016 then TASE2 = TASE * 1.03;
if year = 2017 then TASE2 = TASE * 1.02;
if year = 2018 then TASE2 = TASE * 1.01;
if year = 2019 then TASE2 = TASE * 1;


keep firm year tplv tase2 lag_TASE omavar kopo opo current INDUSTRY_TIL verot nettulos INDUSTRY2_2;

rename TPLV = LV TASE2 = TASE OMAVAR = OMAVARAISUUS kopo = ROA opo = ROE current = CURRENT_RATIO INDUSTRY_TIL = INDUSTRY INDUSTRY2_2 = INDUSTRY_2002;
 
run;

* Position data;

data positiot;
set lib100.positions;

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 positiot_2;
set positiot positiot_a;
run;

proc sort nodupkey data = positiot_2;
by id firm year asema;
run;

data positiot_3;
set positiot_2;
run;

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

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

proc transpose data = positiot_3 out = positiot_firms prefix = id;
by firm year;
var id;

run;

data positiot_firms_2;
set positiot_firms;

if id2 ^= '' then delete;

keep firm year id1;

run;

* CEOs;

data ceos;
set lib100.ceos;

if year < 2016;

CEO = 1;

keep id firm year CEO;

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

CEO = 1;

keep id firm year CEO;

run;

data ceos_2;
set ceos ceos_a;
run;

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

* CEO duality;

data positiot_hallitus;
set lib100.positions; 

if asema = 'J' or asema = 'PJ';

keep id firm year;

run;

data positiot_hallitus_a;
set lib100.positions_asiakastieto_070824; 

if 2016 <= year <= 2019;

if asema = 'Varsinainen jäsen' or asema = 'Puheenjohtaja';

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

keep id firm year;

run;

data positiot_duality;
set positiot_hallitus positiot_hallitus_a;

run;

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

proc transpose data = positiot_duality out = positiot_duality_2 prefix = id;
by FIRM YEAR;
var id;

run;

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

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

data positiot_duality_3;
merge positiot_duality_2 ceos_2;
by firm year;

if id = '' then delete;

drop CEO;

run;

data positiot_duality_4;
set positiot_duality_3;

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

keep firm year id CEO_Duality;

run;

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

* Demografic data;

data demografiset;
 set lib100.demografiset_220524;

 if year >= 1987;
 
 if MARITAL_STATUS = 2 then D_MARITAL_STATUS = 0;
 if MARITAL_STATUS = 1 or 
 MARITAL_STATUS = 4 or
 MARITAL_STATUS = 5 then D_MARITAL_STATUS = 1;

 if Avoliitto = 1 then D_MARITAL_STATUS = 0;

 if ID ^= lag (ID) then do; 
	CUM_Sum_divorce = 0; 
	CUM_Sum_Married = 0;

	end;

 CUM_Sum_divorce + Divorce;
 CUM_Sum_Married + Married;

 KUNTA = MUNICIPALITY*1;
 KUNTA2 = MUNICIPALITY2*1;

 keep id year D_MARITAL_STATUS AGE BIRTHYEAR MALE MARITAL_STATUS NUM_CHILDREN_UNDER_18 Divorce Married
Divorce_prev_5_years CUM_Sum_divorce CUM_Sum_married Avoliitto MAAKUNTA KUNTA KUNTA2;

run;

data demografiset_maakunta;
 set lib100.demografiset_220524;

if MAAKUNTA = . then delete;

keep id YEAR MAAKUNTA;
rename YEAR = YEAR_FIRST MAAKUNTA = MAAKUNTA_FIRST;

run;

proc sort data = demografiset_maakunta;
by id YEAR_FIRST;
run;

proc sort nodupkey data = demografiset_maakunta;
by id;
run;

data demografiset_maakunta_2;
set demografiset_maakunta;
keep id MAAKUNTA_FIRST;
run;

data demografiset_87;
 set lib100.demografiset_220524;

if MAAKUNTA = . then delete;
KUNTA2 = MUNICIPALITY2*1;

keep id YEAR KUNTA2;
rename KUNTA2 = KUNTA_FIRST YEAR = YEAR_FIRST;

run;

data demografiset_80_86;
 set lib100.demografiset_71_86_240524;

if year >= 1980;

if MAAKUNTA = . then delete;
KUNTA2 = MUNICIPALITY2*1;

keep id YEAR KUNTA2;
rename KUNTA2 = KUNTA_FIRST YEAR = YEAR_FIRST;

run;

data demografiset_ensim;
set demografiset_87 demografiset_80_86;
run;

proc sort data = demografiset_ensim;
by id YEAR_FIRST;
run;

proc sort nodupkey data = demografiset_ensim;
by id;
run;

data demografiset_ensim_2;
merge demografiset_ensim demografiset_maakunta_2;
by id;
run;

data demografiset_ensim_3;
set demografiset_ensim_2;

if MAAKUNTA_FIRST = . then delete;

keep id KUNTA_FIRST MAAKUNTA_FIRST YEAR_FIRST;
run;

data demografiset_17;
 set lib100.demografiset_220524;

if AGE = 17;
if MAAKUNTA = . then delete;
KUNTA2 = MUNICIPALITY2*1;

keep id YEAR MAAKUNTA KUNTA2;
rename KUNTA2 = KUNTA_17 MAAKUNTA = MAAKUNTA_17 YEAR = YEAR_17;

run;

proc means noprint sum data = demografiset;
var Divorce Married;

by id;

output out = Marital_sums sum = Lifetime_Sum_Divorce Lifetime_Sum_Married;

run;

data demografiset_2;
merge demografiset Marital_sums;

by id;

CUM_Sum_divorce2 = lag(CUM_Sum_divorce);
CUM_Sum_Married2 = lag(CUM_Sum_Married);

if ID ^= lag (ID) then CUM_Sum_divorce2 = .;
if ID ^= lag (ID) then CUM_Sum_Married2 = .;

if year > 1993;

drop _type_ _freq_;

run;

* Education data;

data Koulutus;
 set lib100.Education_170524;

 if year > 1993;
 
keep id year edulevel business_socialdeg;

run;

* Income data;

data Tulodata;
set lib100.henkiloverotus_220925;

if year > 1993;

* Inflation adjustment ;

if year = 1994 then TOTALINCOME2 = TOTALINCOME * 1.43;
if year = 1995 then TOTALINCOME2 = TOTALINCOME * 1.42;
if year = 1996 then TOTALINCOME2 = TOTALINCOME * 1.41;
if year = 1997 then TOTALINCOME2 = TOTALINCOME * 1.39;
if year = 1998 then TOTALINCOME2 = TOTALINCOME * 1.37;
if year = 1999 then TOTALINCOME2 = TOTALINCOME * 1.36;
if year = 2000 then TOTALINCOME2 = TOTALINCOME * 1.31;
if year = 2001 then TOTALINCOME2 = TOTALINCOME * 1.28;
if year = 2002 then TOTALINCOME2 = TOTALINCOME * 1.26;
if year = 2003 then TOTALINCOME2 = TOTALINCOME * 1.25;
if year = 2004 then TOTALINCOME2 = TOTALINCOME * 1.25;
if year = 2005 then TOTALINCOME2 = TOTALINCOME * 1.23;
if year = 2006 then TOTALINCOME2 = TOTALINCOME * 1.21;
if year = 2007 then TOTALINCOME2 = TOTALINCOME * 1.18;
if year = 2008 then TOTALINCOME2 = TOTALINCOME * 1.14;
if year = 2009 then TOTALINCOME2 = TOTALINCOME * 1.14;
if year = 2010 then TOTALINCOME2 = TOTALINCOME * 1.12;
if year = 2011 then TOTALINCOME2 = TOTALINCOME * 1.09;
if year = 2012 then TOTALINCOME2 = TOTALINCOME * 1.06;
if year = 2013 then TOTALINCOME2 = TOTALINCOME * 1.04;
if year = 2014 then TOTALINCOME2 = TOTALINCOME * 1.03;
if year = 2015 then TOTALINCOME2 = TOTALINCOME * 1.03;
if year = 2016 then TOTALINCOME2 = TOTALINCOME * 1.03;
if year = 2017 then TOTALINCOME2 = TOTALINCOME * 1.02;
if year = 2018 then TOTALINCOME2 = TOTALINCOME * 1.01;
if year = 2019 then TOTALINCOME2 = TOTALINCOME * 1;

LN_TOTALINCOME = log(TOTALINCOME2 + 1);

keep id year salarytotal LN_TOTALINCOME totalincome2 capitaltotal /*wealth1*/;

rename totalincome2 = totalincome;

run;

* Ownership data;

data omistusosuus;
set lib100.osakkeenomistajat_data_230925;

keep firm year id percentageown;

run;

data omistusosuus_firma;
set lib100.osakkeenomistajat_data_230925;

omistus_firma_mukaan = 1;

keep firm year omistus_firma_mukaan;

run;

* Crime data;

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

YEAR2 = YEAR - 1;

drop year;
rename YEAR2 = YEAR;

run;

data Tuomioiden_rang;
set lib100.Tuomioiden_rangaistukset_050924;

YEAR2 = YEAR - 1;

drop year;
rename YEAR2 = YEAR;
run;

data kirjanpitorikos_ennen_periodi;
 set lib100.convictions_kp_tekovuosi_300924;    	/* Based on the year of committing a crime*/
 
if year = 1993;

if CUM_Talous_kaikki > 0 then Talous_ennen_periodia = 1;

keep id Talous_ennen_periodia;

run;

* Mental disorder diagnosis data;

data hilmot;
set lib100.personyear_avo_180324;

if year > 1993;

D_CUM_SUBSTANCE = 0;
D_CUM_ALL_BUT_SUBS = 0;

if CUM_NSUBSTANCE > 0 then D_CUM_SUBSTANCE = 1;
if CUM_NALL_BUT_SUBS > 0 then D_CUM_ALL_BUT_SUBS = 1;

keep id year D_CUM_SUBSTANCE D_CUM_ALL_BUT_SUBS CUM_NALL_BUT_SUBS CUM_NSUBSTANCE;

run;

* Combines accounting data to position data;

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

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

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

data acc_positiot;
merge acc_2 positiot_2 positiot_firms_2 omistusosuus_firma;
by firm year;

if firm = '' then delete;
if id = '' then delete;
if LV = . then delete;
if omavaraisuus = . then delete;

run;

* Combines all the data;

proc sort data = demografiset_2;
by id year;
run;

proc sort data = koulutus;
by id year;
run;

proc sort data = demografiset_ensim_3;
by id;
run;

proc sort data = demografiset_17;
by id;
run;

proc sort data = tulodata;
by id year;
run;

proc sort data = hilmot;
by id year;
run;

proc sort data = kirjanpitorikos_yt;
by id year;
run;

proc sort data = tuomioiden_rang;
by id year;
run;

proc sort data = acc_positiot;
by id year;
run;

data kirjanpitodata;
merge acc_positiot demografiset_2 koulutus tulodata hilmot kirjanpitorikos_yt tuomioiden_rang;
by id year;

if male = . then delete;
if birthyear = . then delete;

if firm = '' then delete;
if id = '' then delete;
if LV = . then delete;
if omavaraisuus = . then delete;

LNTASE = log(TASE);

if CUM_Muu3_tuomio = . then CUM_Muu3_tuomio = 0;
if CUM_Muu4_tuomio = . then CUM_Muu4_tuomio = 0;
if CUM_Muu5_tuomio = . then CUM_Muu5_tuomio = 0;
if CUM_Talous_tuomio = . then CUM_Talous_tuomio = 0;
if CUM_Talous_kaikki_tuomio = . then CUM_Talous_kaikki_tuomio = 0;
if CUM_henkiterv_tuomio = . then CUM_henkiterv_tuomio = 0;
if CUM_LiikenneRR_tuomio = . then CUM_LiikenneRR_tuomio = 0;
if CUM_Rattijuopumus_tuomio = . then CUM_Rattijuopumus_tuomio = 0;
if CUM_Kirjanpitorikos_tuomio = . then CUM_Kirjanpitorikos_tuomio = 0;

if CUM_Finan_rep_fraud = . then CUM_Finan_rep_fraud = 0;
if CUM_Tax_relat_fraud = . then CUM_Tax_relat_fraud = 0;
if CUM_Other_finan_crime = . then CUM_Other_finan_crime = 0;
if CUM_Finan_rep_fraud_tuomio = . then CUM_Finan_rep_fraud_tuomio= 0;
if CUM_Tax_relat_fraud_tuomio = . then CUM_Tax_relat_fraud_tuomio = 0;
if CUM_Other_finan_crime_tuomio = . then CUM_Other_finan_crime_tuomio = 0;

if CUM_Muu_rikos_4_tuomio_2 = . then CUM_Muu_rikos_4_tuomio_2 = 0;
if CUM_Talous_kaikki_tuomio_2 = . then CUM_Talous_kaikki_tuomio_2 = 0;

if CUM_Muu3 = . then CUM_Muu3 = 0;
if CUM_Muu4 = . then CUM_Muu4 = 0;
if CUM_Muu5 = . then CUM_Muu5 = 0;
if CUM_Talous = . then CUM_Talous = 0;
if CUM_Talous_kaikki = . then CUM_Talous_kaikki = 0;

if CUM_Muu_rikos_4_2 = . then CUM_Muu_rikos_4_2 = 0;
if CUM_Talous_kaikki_2 = . then CUM_Talous_kaikki_2 = 0;

if Lifetime_CUM_Muu3 = . then Lifetime_CUM_Muu3 = 0;
if Lifetime_CUM_Muu4 = . then Lifetime_CUM_Muu4 = 0;
if Lifetime_CUM_Muu5 = . then Lifetime_CUM_Muu5 = 0;
if Lifetime_CUM_Talous = . then Lifetime_CUM_Talous = 0;
if Lifetime_CUM_Talous_kaikki = . then Lifetime_CUM_Talous_kaikki = 0;
if Lifetime_CUM_Henkiterv = . then Lifetime_CUM_Henkiterv = 0;
if Lifetime_CUM_Liikenne = . then Lifetime_CUM_Liikenne = 0;
if Lifetime_CUM_KPrikos_kaikki = . then Lifetime_CUM_KPrikos_kaikki = 0;
if Lifetime_CUM_Finan_rep_fraud = . then Lifetime_CUM_Finan_rep_fraud = 0;
if Lifetime_CUM_Tax_relat_fraud = . then Lifetime_CUM_Tax_relat_fraud = 0;
if Lifetime_CUM_Other_finan_crime = . then Lifetime_CUM_Other_finan_crime = 0;

if D_Lifetime_Kirjpitorikos = . then D_Lifetime_Kirjpitorikos = 0;
if D_Lifetime_Muu_rikos_3 = . then D_Lifetime_Muu_rikos_3 = 0;
if D_Lifetime_Muu_rikos_3_tuomio = . then D_Lifetime_Muu_rikos_3_tuomio = 0;
if D_Lifetime_Muu_rikos_4 = . then D_Lifetime_Muu_rikos_4 = 0;
if D_Lifetime_Muu_rikos_4_tuomio = . then D_Lifetime_Muu_rikos_4_tuomio = 0;
if D_Lifetime_Muu_rikos_5 = . then D_Lifetime_Muu_rikos_5 = 0;
if D_Lifetime_Muu_rikos_5_tuomio = . then D_Lifetime_Muu_rikos_5_tuomio = 0;
if D_Lifetime_Talous = . then D_Lifetime_Talous = 0;
if D_Lifetime_Talous_tuomio = . then D_Lifetime_Talous_tuomio = 0;
if D_Lifetime_Talous_kaikki = . then D_Lifetime_Talous_kaikki = 0;
if D_Lifetime_Talous_kaikki_tuomio = . then D_Lifetime_Talous_kaikki_tuomio = 0;
if D_Lifetime_Henkiterv = . then D_Lifetime_Henkiterv = 0;
if D_Lifetime_Henkiterv_tuomio = . then D_Lifetime_Henkiterv_tuomio = 0;
if D_Lifetime_Liikenne = . then D_Lifetime_Liikenne = 0;
if D_Lifetime_Liikenne_tuomio = . then D_Lifetime_Liikenne_tuomio = 0;
if D_Lifetime_DUI = . then D_Lifetime_DUI = 0;
if D_Lifetime_DUI_tuomio = . then D_Lifetime_DUI_tuomio = 0;
if D_Lifetime_Finan_fraud = . then D_Lifetime_Finan_fraud = 0;
if D_Lifetime_Tax_fraud = . then D_Lifetime_Tax_fraud = 0;
if D_Lifetime_other_fraud = . then D_Lifetime_other_fraud = 0;

if D_Kirjanpitorikos_tuomio = . then D_Kirjanpitorikos_tuomio = 0;
if D_Kirjanpitorikos_kaikki = . then D_Kirjanpitorikos_kaikki = 0;
if D_Kirjanpitorikos = . then D_Kirjanpitorikos = 0;
if D_KPrikos_rlaki_tuomio = . then D_KPrikos_rlaki_tuomio = 0;
if D_KPRikos_rlaki = . then D_KPRikos_rlaki = 0;
if D_Muu_rikos_3 = . then D_Muu_rikos_3 = 0;
if D_Muu_rikos_3_tuomio = . then D_Muu_rikos_3_tuomio = 0;
if D_Muu_rikos_4 = . then D_Muu_rikos_4 = 0;
if D_Muu_rikos_4_tuomio = . then D_Muu_rikos_4_tuomio = 0;
if D_Muu_rikos_5 = . then D_Muu_rikos_5 = 0;
if D_Muu_rikos_5_tuomio = . then D_Muu_rikos_5_tuomio = 0;
if D_Talous = . then D_Talous = 0;
if D_Talous_tuomio = . then D_Talous_tuomio = 0;
if D_Talous_kaikki = . then D_Talous_kaikki = 0;
if D_Talous_kaikki_tuomio = . then D_Talous_kaikki_tuomio = 0;
if D_Henkiterv = . then D_Henkiterv = 0;
if D_Henkiterv_tuomio = . then D_Henkiterv_tuomio = 0;
if D_Liikenne = . then D_Liikenne = 0;
if D_Liikenne_tuomio = . then D_Liikenne_tuomio = 0;
if D_Finan_rep_fraud = . then D_Finan_rep_fraud = 0;
if D_Tax_relat_fraud = . then D_Tax_relat_fraud = 0;
if D_Other_finan_crime = . then D_Other_finan_crime = 0;
if D_Finan_rep_fraud_tuomio = . then D_Finan_rep_fraud_tuomio = 0;
if D_Tax_relat_fraud_tuomio = . then D_Tax_relat_fraud_tuomio = 0;
if D_Other_finan_crime_tuomio = . then D_Other_finan_crime_tuomio = 0;

if D_CUM_SUBSTANCE = . then D_CUM_SUBSTANCE = 0;
if D_CUM_ALL_BUT_SUBS = . then D_CUM_ALL_BUT_SUBS = 0;
if CUM_NALL_BUT_SUBS = . then CUM_NALL_BUT_SUBS = 0;
if CUM_NSUBSTANCE = . then CUM_NSUBSTANCE = 0;

if D_Lifetime_Talous_kaikki_v = . then D_Lifetime_Talous_kaikki_v = 0;
if D_Lifetime_Talous_kaikki_s = . then D_Lifetime_Talous_kaikki_s = 0;
if D_Lifetime_Talous_kaikki_ehdol_v = . then D_Lifetime_Talous_kaikki_ehdol_v = 0;
if D_Lifetime_Talous_kaikki_ehdot_v = . then D_Lifetime_Talous_kaikki_ehdot_v = 0;
if D_Lifetime_muu_rikos_4_v = . then D_Lifetime_muu_rikos_4_v = 0;
if D_Lifetime_muu_rikos_4_s = . then D_Lifetime_muu_rikos_4_s = 0;

if Lifetime_CUM_Talous_kaikki_v = . then Lifetime_CUM_Talous_kaikki_v = 0;
if Lifetime_CUM_Talous_kaikki_s = . then Lifetime_CUM_Talous_kaikki_s = 0;
if Ltime_CUM_Talous_kaikki_ehdol_v = . then Ltime_CUM_Talous_kaikki_ehdol_v = 0;
if Ltime_CUM_Talous_kaikki_ehdot_v = . then Ltime_CUM_Talous_kaikki_ehdot_v = 0;
if Lifetime_CUM_muu_rikos_4_v = . then Lifetime_CUM_muu_rikos_4_v = 0;
if Lifetime_CUM_muu_rikos_4_s = . then Lifetime_CUM_muu_rikos_4_s = 0;

run;

* Combines person-level data;

proc sort data = kirjanpitodata;
by id;
run;

data Kirjanpitodata_2;
merge Kirjanpitodata demografiset_ensim_3 demografiset_17 kirjanpitorikos_ennen_periodi;
by id;

if Talous_ennen_periodia = . then Talous_ennen_periodia = 0;

if id = '' then delete;
if firm = '' then delete;

run;

data crimes_on_board;
set lib100.board_and_ceo_crimes_260925;
run;

data familymember_on_board;
set lib100.familymember_on_board_220925;
run;

* Combines FIRM-YEAR-ID-level data;

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

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

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

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

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

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


data kirjanpitodata_3;
merge kirjanpitodata_2 omistusosuus ceos_2 positiot_duality_4 familymember_on_board crimes_on_board;
by firm year id;

if CEO = . then CEO = 0;
if familymember_on_board = . then familymember_on_board = 0;

if CEO_Duality = . then CEO_Duality = 0;
if asema = 'PJ' and CEO = 1 then CEO_Duality = 1;

if id = '' then delete;
if LV = . then delete;

run;

* Add boardmembers to data;

data boardmembers;
set positiot_2;

if asema = 'PJ' or asema = 'J';

n = 1;

keep id firm year asema n;

run;

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

proc means noprint sum data = boardmembers;
var n;
by firm year;
output out = Board_size sum = N_Board;
run;

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

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

* Ownership data;

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

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

data positiot_omistus;
merge positiot_2 omistusosuus;
by firm year id;

if year >= 2006;

if id = '' then delete;
if asema = '' then delete;

run;

proc means noprint sum data = positiot_omistus;
var percentageown;
by firm year;
output out = SUM_owned sum = SUM_percentageown;
run;

data CEOs_own;
set CEOs_2;
keep id firm year CEO;
run;

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

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

data omistus_CEO;
merge CEOs_own omistusosuus;
by firm year id;

if year >= 2006;
if CEO = . then delete;

keep FIRM YEAR PERCENTAGEOWN;
rename PERCENTAGEOWN = CEO_PERCENTAGEOWN;

run;

data kirjanpitodata_4;
merge kirjanpitodata_3 Board_size SUM_owned omistus_CEO;
by firm year;

if id = '' then delete;
if LV = . then delete;

if YEAR >= 2006 and percentageown = . and omistus_firma_mukaan = 1 then percentageown = 0;
if YEAR >= 2006 and SUM_percentageown = . and omistus_firma_mukaan = 1 then SUM_percentageown = 0;
if YEAR >= 2006 and CEO_percentageown = . and omistus_firma_mukaan = 1 then CEO_percentageown = 0;

drop _type_ _freq_;

run;

proc means noprint data = kirjanpitodata_4;
by firm;
var familymember_on_board familymember_CEO;
output out = fmob_firm mean = Mean_fmob_firm Mean_fmCEO_firm;
run;

data kirjanpitodata_5;
merge kirjanpitodata_4 fmob_firm;
by firm;

if id = '' then delete;
if LV = . then delete;

drop _type_ _freq_;

run;

proc sort data = kirjanpitodata_5;
by id;
run;

proc means noprint data = kirjanpitodata_5;
by id;
var familymember_on_board familymember_CEO;
output out = fmob mean = Mean_fmob Mean_fmCEO;
run;

data kirjanpitodata_6;
merge kirjanpitodata_5 fmob;
by id;

if id = '' then delete;
if LV = . then delete;

drop _type_ _freq_;

run;

* Crime data at municipality level;

data Rikokset_kunta_vuosi;
set lib100.rikokset_kunta_vuosi_050624;

rename Kunta = Kunta2;
run;

data Rikokset_kunta;
set lib100.rikokset_kunta_110624;

rename kunta = kunta2;
run;

data Rikokset_kunta_17;
set lib100.rikokset_kunta_vuosi_050624;

rename kunta = kunta_17 year = year_17 kaikki_rikokset_vuosi = kaikki_rikokset_17 talous_rikokset_vuosi = talous_rikokset_17 muut_rikokset_vuosi = muut_rikokset_17;
run;

data Rikokset_kunta_ensim;
set lib100.rikokset_kunta_vuosi_050624;

rename kunta = kunta_first year = year_first kaikki_rikokset_vuosi = kaikki_rikokset_ensim talous_rikokset_vuosi = talous_rikokset_ensim muut_rikokset_vuosi = muut_rikokset_ensim;
run;

proc sort data = Rikokset_kunta_vuosi;
by year kunta2;
run;

proc sort data = kirjanpitodata_6;
by year kunta2;
run; 

data kirjanpitodata_7;
merge kirjanpitodata_6 Rikokset_kunta_vuosi;
by year kunta2;

if id = '' then delete;

run;

proc sort data = kirjanpitodata_7;
by kunta2;
run;

data kirjanpitodata_8;
merge kirjanpitodata_7 Rikokset_kunta;
by kunta2;

if id = '' then delete;

run;

proc sort data = Rikokset_kunta_17;
by year_17 kunta_17;
run; 

proc sort data = kirjanpitodata_8;
by year_17 kunta_17;
run; 

data kirjanpitodata_9;
merge kirjanpitodata_8 Rikokset_kunta_17;
by year_17 kunta_17;

if id = '' then delete;

run;

proc sort data = Rikokset_kunta_ensim;
by year_first kunta_first;
run; 

proc sort data = kirjanpitodata_9;
by year_first kunta_first;
run; 

data kirjanpitodata_10;
merge kirjanpitodata_9 Rikokset_kunta_ensim;
by year_first kunta_first;

if id = '' then delete;

run;

data Rikokset_maakunta_vuosi;
set lib100.rikokset_maakunta_vuosi_050624;

run;

proc sort data = Rikokset_maakunta_vuosi;
by year maakunta;
run; 

proc sort data = kirjanpitodata_10;
by year maakunta;
run; 

data kirjanpitodata_11;
merge kirjanpitodata_10 Rikokset_maakunta_vuosi;
by year maakunta;

if id = '' then delete;

run;


* Saves data;

data lib100.Kirjanpitodata_tekovuosi1_220925;
set kirjanpitodata_11;
run;

































