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

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

* Initial sample on executives;

data tutkimushenkilot;
set lib100.stata_talous_011025;

mukana = 1;

keep id mukana D_Vaste_talous_kaikki;

run;

proc means noprint data = tutkimushenkilot;
by id;
var D_Vaste_talous_kaikki;
output out = Mean_rikokset mean = Mean_D_Vaste_talous_kaikki;
run;

data tutkimushenkilot_2;
merge tutkimushenkilot Mean_rikokset;
by id;
run;

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

* Executives' spouses;

data puolisot;
set lib24.u0599_kohde_puoliso_87_22_s;

if puolison_shnro = '' then delete;

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

proc sort data = tutkimushenkilot_2;
by id;
run;

proc sort data = puolisot;
by id;
run;

data puolisot_2;
merge tutkimushenkilot_2 puolisot;
by id;

if mukana = 1;
if id_pa = '' then delete;
if year < 2020;

drop _type_ _freq_;

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;

KUNTA2 = MUNICIPALITY2*1;

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

keep id year LN_AGE LN_CUM_Sum_divorce KUNTA2 Birthyear MALE MAAKUNTA;

run;

data demografiset_pa;
 set demografiset;

keep id year LN_AGE LN_CUM_Sum_divorce KUNTA2 Birthyear MALE MAAKUNTA;

rename id = id_pa LN_AGE = Pa_LN_AGE LN_CUM_Sum_divorce = Pa_LN_CUM_Sum_divorce KUNTA2 = Pa_KUNTA2 Birthyear = Pa_Birthyear MAAKUNTA = Pa_MAAKUNTA MALE = Pa_MALE;

run;

* Education data;

data Koulutus;
 set lib100.Education_170524;

keep id year EDULEVEL business_socialdeg;

run;

data Koulutus_pa;
 set koulutus;

keep id year EDULEVEL business_socialdeg;

rename id = id_pa EDULEVEL = Pa_EDULEVEL business_socialdeg = Pa_business_socialdeg;

run;

* Income data;

data Tulodata;
set lib100.henkiloverotus_220925;

* Inflation adjustment;

if year = 1987 then TOTALINCOME2 = TOTALINCOME * 1.87;
if year = 1988 then TOTALINCOME2 = TOTALINCOME * 1.78;
if year = 1989 then TOTALINCOME2 = TOTALINCOME * 1.67;
if year = 1990 then TOTALINCOME2 = TOTALINCOME * 1.58;
if year = 1991 then TOTALINCOME2 = TOTALINCOME * 1.51;
if year = 1992 then TOTALINCOME2 = TOTALINCOME * 1.48;
if year = 1993 then TOTALINCOME2 = TOTALINCOME * 1.45;
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 ;

run;

data Tulodata_pa;
set tulodata;

keep id year LN_TOTALINCOME;

rename id = id_pa LN_TOTALINCOME = Pa_LN_TOTALINCOME;

run;

data kirjanpitorikos_yt;

set lib100.convictions_kp_tekovuosi_300924;   

if CUM_Talous_kaikki = . then CUM_Talous_kaikki = 0;

Year = YEAR - 1;

keep id year D_talous_kaikki D_muu_rikos_4 CUM_Talous_kaikki;

run;

data kirjanpitorikos_yt_pa;

set kirjanpitorikos_yt;    

if CUM_Talous_kaikki = . then CUM_Talous_kaikki = 0;

keep id year D_talous_kaikki D_muu_rikos_4 CUM_Talous_kaikki;
rename id = id_pa D_talous_kaikki = Pa_D_talous_kaikki D_muu_rikos_4 = Pa_D_muu_rikos_4 CUM_Talous_kaikki = Pa_CUM_Talous_kaikki;

run;

* Mental disorder diagnosis data;

data hilmot;
set lib100.personyear_avo_180324;

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;

data hilmot_pa;
set hilmot;

keep id year D_CUM_SUBSTANCE D_CUM_ALL_BUT_SUBS;

rename id = id_pa D_CUM_SUBSTANCE = Pa_D_CUM_SUBSTANCE D_CUM_ALL_BUT_SUBS = Pa_D_CUM_ALL_BUT_SUBS;

run;

proc sort data = puolisot_2;
by id id_pa year;
run;

proc means noprint data = puolisot_2;
var mukana;
by id id_pa;
output out = Puolisot_vuosia mean = Mean_mukana;
run;

data puolisot_3;
merge puolisot_2 Puolisot_vuosia;
by id id_pa;

if _freq_ = 1 then delete;

rename _freq_ = Pa_Length;

run;

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

data puolisot_4;
set puolisot_3;

if id = lag(id) and id_pa ^= lag(id_pa) then mukaan = 1;
if id ^= lag(id) then mukaan = 1;

run;

proc sort data = puolisot_4;
by id id_pa descending year;
run;

data puolisot_5;
set puolisot_4;

if id = lag(id) and id_pa ^= lag(id_pa) then mukaan = 1;
if id ^= lag(id) then mukaan = 1;

if mukaan = 1;

YEAR = YEAR - 1;

run;

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

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

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

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

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

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

data puolisot_data;
merge puolisot_5 kirjanpitorikos_yt hilmot tulodata koulutus demografiset;
by id year;

if mukana = 1;

run;

proc sort data = kirjanpitorikos_yt_pa;
by id_pa year;
run;

proc sort data = hilmot_pa;
by id_pa year;
run;

proc sort data = tulodata_pa;
by id_pa year;
run;

proc sort data = koulutus_pa;
by id_pa year;
run;

proc sort data = demografiset_pa;
by id_pa year;
run;

proc sort data = puolisot_data;
by id_pa year;
run;

data puolisot_data_2;
merge puolisot_data kirjanpitorikos_yt_pa hilmot_pa tulodata_pa koulutus_pa demografiset_pa;
by id_pa year;

if mukana = 1;

if D_talous_kaikki = . then D_talous_kaikki = 0;
if Pa_D_talous_kaikki = . then Pa_D_talous_kaikki = 0;

if D_muu_rikos_4 = . then D_muu_rikos_4 = 0;
if Pa_D_muu_rikos_4 = . then Pa_D_muu_rikos_4 = 0;

if edulevel = . then edulevel = 0;
if Pa_edulevel = . then Pa_edulevel = 0;

if business_socialdeg = . then business_socialdeg = 0;
if Pa_business_socialdeg = . then Pa_business_socialdeg = 0;

if LN_TOTALINCOME = . then LN_TOTALINCOME = 0;
if Pa_LN_TOTALINCOME = . then Pa_LN_TOTALINCOME = 0;

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

if Pa_D_CUM_SUBSTANCE = . then Pa_D_CUM_SUBSTANCE = 0;
if Pa_D_CUM_ALL_BUT_SUBS = . then Pa_D_CUM_ALL_BUT_SUBS = 0;

LN_EDULEVEL = log(EDULEVEL + 1);
Pa_LN_EDULEVEL = log(Pa_EDULEVEL + 1);

Same_municipality = 0;

if KUNTA2 = Pa_KUNTA2 then Same_municipality = 1;

run;

data Puoliso;
set lib100.stata_talous_011025;

if puoliso = 1;

mukaaan = 1;

keep id mukaaan;
run;

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

proc sort data = puolisot_data_2;
by id;
run;

data puolisot_data_3;
merge puolisot_data_2 puoliso;
by id;

YEAR = YEAR + 1;

if LN_AGE = . or Pa_LN_AGE = . then delete;
if CUM_Talous_kaikki = . then CUM_Talous_kaikki = 0;
if Pa_CUM_Talous_kaikki = . then Pa_CUM_Talous_kaikki = 0;

run;

proc sort data = puolisot_data_3;
by id id_pa YEAR;
run;

data puolisot_data_4;
set puolisot_data_3;

LAG_D_Talous_kaikki = lag(D_Talous_kaikki); LAG_Pa_D_Talous_kaikki = lag(Pa_D_Talous_kaikki); LAG_Pa_D_muu_rikos_4 = lag(Pa_D_muu_rikos_4); LAG_Pa_LN_EDULEVEL = lag(Pa_LN_EDULEVEL); LAG_Pa_business_socialdeg = lag(Pa_business_socialdeg); LAG_Pa_LN_CUM_Sum_divorce = lag(Pa_LN_CUM_Sum_divorce);
LAG_Pa_LN_TOTALINCOME = lag(Pa_LN_TOTALINCOME); LAG_Pa_D_CUM_SUBSTANCE = lag(Pa_D_CUM_SUBSTANCE); LAG_Pa_D_CUM_ALL_BUT_SUBS = lag(Pa_D_CUM_ALL_BUT_SUBS); LAG_Pa_LN_AGE = lag(Pa_LN_AGE); LAG_D_Muu_rikos_4 = lag(D_Muu_rikos_4); LAG_LN_AGE = lag(LN_AGE); LAG_Pa_MALE = lag(Pa_MALE);
LAG_BUSINESS_SOCIALDEG = lag(BUSINESS_SOCIALDEG); LAG_LN_EDULEVEL = lag(LN_EDULEVEL); LAG_LN_CUM_Sum_divorce = lag(LN_CUM_Sum_divorce); LAG_LN_TOTALINCOME = lag(LN_TOTALINCOME); LAG_D_CUM_SUBSTANCE = lag(D_CUM_SUBSTANCE); LAG_D_CUM_ALL_BUT_SUBS = lag(D_CUM_ALL_BUT_SUBS);
LAG_Same_municipality = lag(Same_municipality); LAG_CUM_Talous_kaikki = lag(CUM_Talous_kaikki); LAG_Pa_CUM_Talous_kaikki = lag(Pa_CUM_Talous_kaikki); LAG_MAAKUNTA = lag(MAAKUNTA); LAG_MALE = lag(MALE);

E_FM_Muutos = CUM_Talous_kaikki - LAG_CUM_Talous_kaikki; E_FM_Muutos_dummy = 0; if E_FM_Muutos > 0 then  E_FM_Muutos_dummy = 1;
P_FM_Muutos = Pa_CUM_Talous_kaikki - LAG_Pa_CUM_Talous_kaikki; P_FM_Muutos_dummy = 0; if P_FM_Muutos > 0 then  P_FM_Muutos_dummy = 1;

if id ^= lag(id) or id_pa ^= lag(id_pa) then LAG_D_Talous_kaikki = .;
if lag_D_talous_kaikki = . then delete;
if LAG_MAAKUNTA ^= 21;

LN_Pa_Length = log(Pa_Length);

run;

* Saves the data;

data lib100.Stata_puolisodata_011025;
set puolisot_data_4;
run;


