/* Program reads data on executives' spouses and combines all the data in one dataset */

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

* Intial sample on executives;

data tutkimushenkilot;

set lib100.tutkimushenkilot_id_year_250925;

mukana = 1;

keep id year mukana;
rename id = id_th;

run;

* Executives' spouses;

data puolisot;
set lib24.u0599_kohde_puoliso_87_22_s;

if puolison_shnro = '' then delete;

rename shnro = id_th puolison_shnro = id vuosi = YEAR;
run;

proc sort data = tutkimushenkilot;
by id_th year;
run;

proc sort data = puolisot;
by id_th year;
run;

data puolisot_2;
set puolisot;

if id_th ne lag(id_th) or id ne lag(id) then Pa_Length = 1;
if id_th = lag(id_th) and id = lag(id) then Pa_Length + 1;

run;

data puolisot_3;
merge tutkimushenkilot puolisot_2;
by id_th year;

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

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;

if CUM_Sum_divorce = . then CUM_Sum_divorce = 0;

keep id year AGE MALE CUM_Sum_divorce;

run;

* Education data;

data Koulutus;
 set lib100.Education_170524;

 if year > 1993;

keep id year edulevel business_socialdeg;

run;

* Salary 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 LN_TOTALINCOME totalincome2;

rename totalincome2 = totalincome;

run;

* Crime data;

data kirjanpitorikos_yt;
set lib100.convictions_kp_tekovuosi_300924;    
 
if year > 1993;
	
D_Muu_rikos_4_2 = 0;
D_Talous_kaikki_2 = 0;
	
D_muu_rikos_4_vankeus_2 = 0;
D_Talous_kaikki_vankeus_2 = 0;

if CUM_Muu4 > 0 then D_Muu_rikos_4_2 = 1;
if CUM_Talous_kaikki > 0 then D_Talous_kaikki_2 = 1;

if CUM_muu_rikos_4_vankeus > 0 then D_muu_rikos_4_vankeus_2 = 1;
if CUM_Talous_kaikki_vankeus > 0 then D_Talous_kaikki_vankeus_2 = 1;
	
keep id year D_Muu_rikos_4_2 D_Talous_kaikki_2 D_muu_rikos_4_vankeus_2 D_Talous_kaikki_vankeus_2;

run;

* Mental disorder diagnoses 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 all the data;

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

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

proc sort data = koulutus;
by id year;
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;

data puolisoiden_datat;
merge puolisot_3 demografiset koulutus tulodata hilmot kirjanpitorikos_yt;
by id year;

if id_th = '' then delete;

if D_Muu_rikos_4_2 = . then D_Muu_rikos_4_2 = 0;
if D_Talous_kaikki_2 = . then D_Talous_kaikki_2 = 0;

if D_muu_rikos_4_vankeus_2 = . then D_muu_rikos_4_vankeus_2 = 0;
if D_Talous_kaikki_vankeus_2 = . then D_Talous_kaikki_vankeus_2 = 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 EDULEVEL = . then EDULEVEL = 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 TOTALINCOME = . then TOTALINCOME = 0;
if LN_TOTALINCOME = . then LN_TOTALINCOME = 0;

if EDULEVEL = . then EDULEVEL = 0;
if business_socialdeg = . then business_socialdeg = 0;

if CUM_Sum_divorce = . then CUM_Sum_divorce = 0;

LN_CUM_Sum_divorce = log(CUM_Sum_divorce + 1);
LN_EDULEVEL = log(EDULEVEL + 1);
LN_AGE = log(AGE + 1);

drop CUM_sum_divorce AGE;

rename id = pa_id D_Muu_rikos_4_2 = Pa_D_Muu_rikos_4_2 D_Talous_kaikki_2 = Pa_D_Talous_kaikki_2 D_muu_rikos_4_vankeus_2 = Pa_D_muu_rikos_4_vankeus_2 D_Talous_kaikki_vankeus_2 = Pa_D_Talous_kaikki_vankeus_2
D_CUM_SUBSTANCE = Pa_D_CUM_SUBSTANCE D_CUM_ALL_BUT_SUBS = Pa_D_CUM_ALL_BUT_SUBS CUM_NALL_BUT_SUBS = Pa_CUM_NALL_BUT_SUBS CUM_NSUBSTANCE = Pa_CUM_NSUBSTANCE LN_AGE = Pa_LN_AGE MALE = Pa_MALE
LN_TOTALINCOME = Pa_LN_TOTALINCOME TOTALINCOME = Pa_TOTALINCOME EDULEVEL = Pa_EDULEVEL LN_EDULEVEL = Pa_LN_EDULEVEL BUSINESS_SOCIALDEG = Pa_BUSINESS_SOCIALDEG LN_CUM_Sum_divorce = Pa_LN_CUM_Sum_divorce;

run;

* Municipality level data;

data demografiset_87;
 set lib100.demografiset_220524;

KUNTA2 = MUNICIPALITY2*1;

keep id YEAR KUNTA2 BIRTHYEAR;

run;

data ika;
 set lib100.demografiset_220524;

keep id BIRTHYEAR;

run;

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

data demografiset_80_86;
 set lib100.demografiset_71_86_240524;

if year >= 1980;

KUNTA2 = MUNICIPALITY2*1;

keep id YEAR KUNTA2;

run;

proc sort data = demografiset_80_86;
by id;
run;

data demografiset_80_86_2;
merge demografiset_80_86 ika;
by id;

if Kunta2 = . then delete;

run; 

data puolisot_4;
 set puolisot_3;

keep id mukana;
rename id = pa_id;

run;

proc sort nodupkey data = puolisot_4;
by pa_id;
run;

data demografiset_kaikki;
 set demografiset_87 demografiset_80_86_2;

AGE = YEAR - BIRTHYEAR;

if 0 < AGE < 18;

rename id = pa_id;

run;

proc sort data = demografiset_kaikki;
by pa_id year;
run;

data demografiset_puolisot;
merge demografiset_kaikki puolisot_4;
by pa_id;

if mukana = 1;
if year = . then delete;

run;

data Rikokset_kunta_vuosi;
set lib100.rikokset_kunta_vuosi_050624;

rename Kunta = Kunta2;
run;

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

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

data demografiset_rikos_kunta;
merge demografiset_puolisot Rikokset_kunta_vuosi;
by kunta2 year;

if pa_id = '' then delete;

run;

proc sort data = demografiset_rikos_kunta;
by pa_id year;
run;

proc means noprint data = demografiset_rikos_kunta;
by pa_id;
var Kaikki_rikokset_vuosi Talous_rikokset_vuosi Muut_rikokset_vuosi;
output out = Rikokset_lapsuus mean = Kaikki_rikokset_lapsuus Talous_rikokset_lapsuus Muut_rikokset_lapsuus;
run;

proc sort data = puolisoiden_datat;
by pa_id;
run;

proc sort data = Rikokset_lapsuus;
by pa_id;
run;

data puolisoiden_datat_2;
merge puolisoiden_datat Rikokset_lapsuus;
by pa_id;

drop _type_ _freq_;
rename Kaikki_rikokset_lapsuus = Pa_Kaikki_rikokset_lapsuus Talous_rikokset_lapsuus = Pa_Talous_rikokset_lapsuus
Muut_rikokset_lapsuus = Pa_Muut_rikokset_lapsuus;

run;

* Saves data;

data lib100.Puolisoiden_datat_011025;
set puolisoiden_datat_2;

if Pa_MALE ^= .;

drop mukana;

rename id_th = id;

run;














