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

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

* Reads position data 2;

data pos;
 set lib3.vasthlo_20210707_s;
 VH_ALKU  = year(datepart(KREK_ALKUPVM));
 VH_LOPPU = year(datepart(KREK_LOPPUPVM));
 ASEMA = ASEMA_NIMI;

 VH_LOPPU_KK = month(datepart(KREK_ALKUPVM));
 VH_LOPPU_PV = day(datepart(KREK_LOPPUPVM));

 if VH_ALKU = 2021 then delete;
 if shnro = '' then delete;
 if KREK_ALKUPVM = '' then delete;

 keep shnro syrtun ASEMA VH_ALKU VH_LOPPU KREK_ALKUPVM KREK_LOPPUPVM;
run;

proc sort nodupkey data = pos;
 by shnro syrtun ASEMA VH_ALKU VH_LOPPU;
run;

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

data pos2;
 set pos;

 if VH_LOPPU =. then VH_LOPPU = 2020;

 Y89 = 1;
 Y90 = 1; Y91 = 1; Y92 = 1; Y93 = 1; 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; Y16 = 1; Y17 = 1; Y18 = 1; Y19 = 1;
 Y20 = 1;																							
 
 C + 1;
 if shnro ne lag (shnro) then C = 1;
 if shnro = lag (shnro) and syrtun ne lag (syrtun) then C = 1;  	

run;

proc sort data = pos2;
 by shnro syrtun C;
run;
 
proc transpose data = pos2 out = pos3;
 var Y89--Y20;
 by shnro syrtun C;
run;

* Cleans tranposed data;
 
data pos4;
 set pos3;

 if _NAME_ = 'Y89' then YEAR = 1989;
 if _NAME_ = 'Y90' then YEAR = 1990;
 if _NAME_ = 'Y91' then YEAR = 1991;
 if _NAME_ = 'Y92' then YEAR = 1992;
 if _NAME_ = 'Y93' then YEAR = 1993;
 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;
 if _NAME_ = 'Y16' then YEAR = 2016;
 if _NAME_ = 'Y17' then YEAR = 2017;
 if _NAME_ = 'Y18' then YEAR = 2018;
 if _NAME_ = 'Y19' then YEAR = 2019;
 if _NAME_ = 'Y20' then YEAR = 2020;

 keep shnro syrtun YEAR C;
run;

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

data bbb;
 merge pos2 pos4;
 by shnro syrtun C;
                                                                                                          
 if VH_ALKU <= YEAR <= VH_LOPPU; 

 keep shnro syrtun YEAR KREK_ALKUPVM KREK_LOPPUPVM ASEMA VH_ALKU VH_LOPPU;
run;

proc sort nodupkey data = bbb;		
 by shnro syrtun YEAR ASEMA descending KREK_ALKUPVM;
run;

proc sort nodupkey data = bbb;
by shnro syrtun YEAR ASEMA;
run;

proc sort data = bbb;
by syrtun;
run;

proc sort data = bbb;
by shnro;
run;


* Saves the data;

data lib100.positions_asiakastieto_070824;
 set bbb;

rename shnro = ID SYRTUN = FIRM;

 run;
