/* Program reads firm-year-CEO/Board data from position data 1 */

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

/* 2016 - 2019 data */

data board_lak_16_19;
set lib2.prh_012016072019_tua_boardlak_11;

if yritysmuoto = 'OY' or yritysmuoto = 'OYJ';

if roolikoodi = 'VJ' then delete;
if roolikoodi = 'VTJ' then delete;
if roolikoodi = 'VPJ' then delete;
if roolikoodi = 'TJS' then delete;

run;

data board_rek_16_19;
set lib2.prh_012016072019_tua_boardrek_11;

if yritysmuoto = 'OY' or yritysmuoto = 'OYJ';

if roolikoodi = 'VJ' then delete;
if roolikoodi = 'VTJ' then delete;
if roolikoodi = 'VPJ' then delete;
if roolikoodi = 'TJS' then delete;

run;

data board_16_19;
set board_rek_16_19 board_lak_16_19;

if syrtun = '' then delete;
if shnro = '' then delete;

drop YRITYKSEN_TILA YR_REKIST_PVM LAKKAAMIS_PVM LAKKAAMISTYYPPI;

run;

data board_16_19_2;
set board_16_19;

 VH_ALKU  = year(datepart(ROOLIN_ALKU_PVM));
 VH_LOPPU = year(datepart(ROOLIN_LOPPU_PVM));

 VH_LOPPU_KK = month(datepart(ROOLIN_LOPPU_PVM));
 VH_LOPPU_PV = day(datepart(ROOLIN_LOPPU_PVM));

 if VH_LOPPU_KK ^= 12 or VH_LOPPU_PV ^= 31 then VH_LOPPU = VH_LOPPU - 1;

 rename syrtun = FIRM shnro = ID ROOLIKOODI = ASEMA;

 run;

 proc sort nodupkey data = board_16_19_2;
 by ID FIRM ASEMA VH_ALKU VH_LOPPU;
 run;

* Creates variables for each year and identifies multiple positions per person per firm ;

data board_16_19_3;
 set board_16_19_2;

 if VH_LOPPU =. then VH_LOPPU = 2019;

 Y16 = 1; Y17 = 1; Y18 = 1; Y19 = 1;				
 
 C + 1;
 if ID ne lag (ID) then C = 1;
 if ID = lag (ID) and FIRM ne lag (FIRM) then C = 1;  		

run;

proc sort data = board_16_19_3;
 by ID FIRM C;
run;
 
proc transpose data = board_16_19_3 out = board_16_19_4;
 var Y16--Y19;
 by ID FIRM C;
run;

* Cleans tranposed data;
 
data board_16_19_5;
 set board_16_19_4;

 if _NAME_ = 'Y16' then YEAR = 2016;
 if _NAME_ = 'Y17' then YEAR = 2017;
 if _NAME_ = 'Y18' then YEAR = 2018;
 if _NAME_ = 'Y19' then YEAR = 2019;

 keep ID FIRM YEAR C;
run;

* Adds data back to the original data and identifies positions for each person/firm/year;

data board_16_19_6;
 merge board_16_19_3 board_16_19_5;
 by ID FIRM C;
                                                                                                         
 if VH_ALKU <= YEAR <= VH_LOPPU; 

 keep ID FIRM YEAR ROOLIN_ALKU_PVM ROOLIN_LOPPU_PVM ASEMA VH_ALKU VH_LOPPU;
run;

proc sort nodupkey data = board_16_19_6;		
 by ID FIRM YEAR ASEMA descending ROOLIN_ALKU_PVM;
run;

proc sort nodupkey data = board_16_19_6;
by ID FIRM YEAR ASEMA;
run;

/* 1994 - 2015 data */

data board_lak_94_15;
set lib2.prh_011994122015_tua_boardlak_1;

if yritysmuoto = 'OY' or yritysmuoto = 'OYJ';

if roolikoodi = 'VJ' then delete;
if roolikoodi = 'VTJ' then delete;
if roolikoodi = 'VPJ' then delete;
if roolikoodi = 'TJS' then delete;

run;

data board_rek_94_15;
set lib2.prh_011994122015_tua_boardrek_1;

if yritysmuoto = 'OY' or yritysmuoto = 'OYJ';

if roolikoodi = 'VJ' then delete;
if roolikoodi = 'VTJ' then delete;
if roolikoodi = 'VPJ' then delete;
if roolikoodi = 'TJS' then delete;

run;

data board_94_15;
set board_rek_94_15 board_lak_94_15;

if syrtun = '' then delete;
if shnro = '' then delete;

drop YRITYKSEN_TILA YR_REKIST_PVM LAKKAAMIS_PVM LAKKAAMISTYYPPI;

run;

data board_94_15_2;
set board_94_15;

 VH_ALKU  = year(datepart(ROOLIN_ALKU_PVM));
 VH_LOPPU = year(datepart(ROOLIN_LOPPU_PVM));

 VH_LOPPU_KK = month(datepart(ROOLIN_LOPPU_PVM));
 VH_LOPPU_PV = day(datepart(ROOLIN_LOPPU_PVM));

 if VH_LOPPU_KK ^= 12 or VH_LOPPU_PV ^= 31 then VH_LOPPU = VH_LOPPU - 1;

 rename syrtun = FIRM shnro = ID ROOLIKOODI = ASEMA;

 run;

 proc sort nodupkey data = board_94_15_2;
 by ID FIRM ASEMA VH_ALKU VH_LOPPU;
 run;

 * Creates variables for each year and identifies multiple positions per person per firm;

 data board_94_15_3;
 set board_94_15_2;

 if VH_LOPPU =. then VH_LOPPU = 2015;

 Y94 = 1; Y95 = 1; Y96 = 1; Y97 = 1; Y98 = 1; Y99 = 1; Y00 = 1;
 Y01 = 1; Y02 = 1; Y03 = 1; Y04 = 1; Y05 = 1; Y06 = 1; Y07 = 1; 
 Y08 = 1; Y09 = 1; Y10 = 1; Y11 = 1; Y12 = 1; Y13 = 1; Y14 = 1; Y15 = 1;	 
 
 C + 1;
 if ID ne lag (ID) then C = 1;
 if ID = lag (ID) and FIRM ne lag (FIRM) then C = 1;  				

run;


proc sort data = board_94_15_3;
 by ID FIRM C;
run;
 
proc transpose data = board_94_15_3 out = board_94_15_4;
 var Y94--Y15;
 by ID FIRM C;
run;

* Cleans tranposed data;
 
data board_94_15_5;
 set board_94_15_4;

 if _NAME_ = 'Y94' then YEAR = 1994;
 if _NAME_ = 'Y95' then YEAR = 1995;
 if _NAME_ = 'Y96' then YEAR = 1996;
 if _NAME_ = 'Y97' then YEAR = 1997;
 if _NAME_ = 'Y98' then YEAR = 1998;
 if _NAME_ = 'Y99' then YEAR = 1999;
 if _NAME_ = 'Y00' then YEAR = 2000;
 if _NAME_ = 'Y01' then YEAR = 2001;
 if _NAME_ = 'Y02' then YEAR = 2002;
 if _NAME_ = 'Y03' then YEAR = 2003;
 if _NAME_ = 'Y04' then YEAR = 2004;
 if _NAME_ = 'Y05' then YEAR = 2005;
 if _NAME_ = 'Y06' then YEAR = 2006;
 if _NAME_ = 'Y07' then YEAR = 2007;
 if _NAME_ = 'Y08' then YEAR = 2008;
 if _NAME_ = 'Y09' then YEAR = 2009;
 if _NAME_ = 'Y10' then YEAR = 2010;
 if _NAME_ = 'Y11' then YEAR = 2011;
 if _NAME_ = 'Y12' then YEAR = 2012;
 if _NAME_ = 'Y13' then YEAR = 2013;
 if _NAME_ = 'Y14' then YEAR = 2014;
 if _NAME_ = 'Y15' then YEAR = 2015;

 keep ID FIRM YEAR C;
run;

* Adds the data back to original data and identifies positions for each person/firm/year;

data board_94_15_6;
 merge board_94_15_3 board_94_15_5;
 by ID FIRM C;
                                                                                                           
 if VH_ALKU <= YEAR <= VH_LOPPU;

 keep ID FIRM YEAR ROOLIN_ALKU_PVM ROOLIN_LOPPU_PVM ASEMA VH_ALKU VH_LOPPU;;
run;

proc sort nodupkey data = board_94_15_6;		
 by ID FIRM YEAR ASEMA descending ROOLIN_ALKU_PVM;
run;

proc sort nodupkey data = board_94_15_6;
by ID FIRM YEAR ASEMA;
run;

* Combines the 1994-2015 and 2016-2019 data;

data board_94_19;
set board_94_15_6 board_16_19_6;
run;

proc sort
nodupkey data = board_94_19;				
 by ID FIRM YEAR ASEMA descending ROOLIN_ALKU_PVM;
run;

proc sort nodupkey data = board_94_19;
by ID FIRM YEAR ASEMA;
run;

* Saves data;

data lib100.positions;
set board_94_19;
run;
