/* Program reads the ownership data from tax returns */

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

* Year 2006;

data s06;
 set lib24.vero6b_9_s_osakas_2006;

 YEAR = 2006;
		
 OSAKELKM_NUM = OSAKELKM*1;					
 if OSAKELKM_NUM = 0 then delete;				

 FIRM = syrtun;

 ID = OSOMASTUNNUS_shnro;

 if ID = '' then delete;

 keep FIRM ID YEAR OSAKELKM_NUM;
 rename OSAKELKM_NUM = OSAKELKM;

run;

proc sort data = s06;
 by ID YEAR FIRM OSAKELKM;
run;

data s06_b;
 set s06; 
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM = lag(OSAKELKM) then delete;
 DUP = 0;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM ne lag(OSAKELKM) then DUP = 1;
run;

data d_06;
 set s06_b;
 if DUP = 0 then delete;
 DEL = 1;
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = d_06;			
 by FIRM YEAR;
run;

* Combines data;

proc sort data = s06_b;
 by FIRM YEAR;
run;

data s06_c;
 merge s06_b d_06;
 by FIRM YEAR;				
 if DEL = 1 then delete;				
 drop DUP DEL;
run;

proc sort nodupkey data = s06_c;
 by FIRM YEAR ID;						
run;

* Year 2007;

data s07;
 set lib24.vero6b_9_s_osakas_2007;

 YEAR = 2007;
		
 OSAKELKM_NUM = OSAKELKM*1;					
 if OSAKELKM_NUM = 0 then delete;				

 FIRM = syrtun;

 ID = OSOMASTUNNUS_shnro;

 if ID = '' then delete;

 keep FIRM ID YEAR OSAKELKM_NUM;
 rename OSAKELKM_NUM = OSAKELKM;

run;

proc sort data = s07;
 by ID YEAR FIRM OSAKELKM;
run;

data s07_b;
 set s07; 
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM = lag(OSAKELKM) then delete;
 DUP = 0;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM ne lag(OSAKELKM) then DUP = 1;
run;

data d_07;
 set s07_b;
 if DUP = 0 then delete;
 DEL = 1;
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = d_07;			
 by FIRM YEAR;
run;

* Combines data;

proc sort data = s07_b;
 by FIRM YEAR;
run;

data s07_c;
 merge s07_b d_07;
 by FIRM YEAR;				
 if DEL = 1 then delete;				
 drop DUP DEL;
run;

proc sort nodupkey data = s07_c;
 by FIRM YEAR ID;						
run;

* Year 2008;

data s08;
 set lib24.vero6b_9_s_osakas_2008;

 YEAR = 2008;
		
 OSAKELKM_NUM = OSAKELKM*1;					
 if OSAKELKM_NUM = 0 then delete;				

 FIRM = syrtun;

 ID = OSOMASTUNNUS_shnro;

 if ID = '' then delete;

 keep FIRM ID YEAR OSAKELKM_NUM;
 rename OSAKELKM_NUM = OSAKELKM;

run;

proc sort data = s08;
 by ID YEAR FIRM OSAKELKM;
run;

data s08_b;
 set s08; 
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM = lag(OSAKELKM) then delete;
 DUP = 0;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM ne lag(OSAKELKM) then DUP = 1;
run;

data d_08;
 set s08_b;
 if DUP = 0 then delete;
 DEL = 1;
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = d_08;			
 by FIRM YEAR;
run;

* Combines data;

proc sort data = s08_b;
 by FIRM YEAR;
run;

data s08_c;
 merge s08_b d_08;
 by FIRM YEAR;				
 if DEL = 1 then delete;				
 drop DUP DEL;
run;

proc sort nodupkey data = s08_c;
 by FIRM YEAR ID;						
run;

* Year 2009;

data s09;
 set lib24.vero6b_9_s_osakas_2009;

 YEAR = 2009;
		
 OSAKELKM_NUM = OSAKELKM*1;					
 if OSAKELKM_NUM = 0 then delete;				

 FIRM = syrtun;

 ID = OSOMASTUNNUS_shnro;

 if ID = '' then delete;

 keep FIRM ID YEAR OSAKELKM_NUM;
 rename OSAKELKM_NUM = OSAKELKM;

run;

proc sort data = s09;
 by ID YEAR FIRM OSAKELKM;
run;

data s09_b;
 set s09; 
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM = lag(OSAKELKM) then delete;
 DUP = 0;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM ne lag(OSAKELKM) then DUP = 1;
run;

data d_09;
 set s09_b;
 if DUP = 0 then delete;
 DEL = 1;
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = d_09;			
 by FIRM YEAR;
run;

* Combines data;

proc sort data = s09_b;
 by FIRM YEAR;
run;

data s09_c;
 merge s09_b d_09;
 by FIRM YEAR;				
 if DEL = 1 then delete;				
 drop DUP DEL;
run;

proc sort nodupkey data = s09_c;
 by FIRM YEAR ID;						
run;

* Year 2010;

data s10;
 set lib24.vero6b_9_s_osakas_2010;

 YEAR = 2010;
		
 OSAKELKM_NUM = OSAKELKM*1;					
 if OSAKELKM_NUM = 0 then delete;				

 FIRM = syrtun;

 ID = OSOMASTUNNUS_shnro;

 if ID = '' then delete;

 keep FIRM ID YEAR OSAKELKM_NUM;
 rename OSAKELKM_NUM = OSAKELKM;

run;

proc sort data = s10;
 by ID YEAR FIRM OSAKELKM;
run;

data s10_b;
 set s10; 
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM = lag(OSAKELKM) then delete;
 DUP = 0;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM ne lag(OSAKELKM) then DUP = 1;
run;

data d_10;
 set s10_b;
 if DUP = 0 then delete;
 DEL = 1;
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = d_10;			
 by FIRM YEAR;
run;

* Combines data;

proc sort data = s10_b;
 by FIRM YEAR;
run;

data s10_c;
 merge s10_b d_10;
 by FIRM YEAR;				
 if DEL = 1 then delete;				
 drop DUP DEL;
run;

proc sort nodupkey data = s10_c;
 by FIRM YEAR ID;						
run;

* Year 2011;

data s11;
 set lib24.vero6b_9_s_osakas_2011;

 YEAR = 2011;
		
 OSAKELKM_NUM = OSAKELKM*1;					
 if OSAKELKM_NUM = 0 then delete;				

 FIRM = syrtun;

 ID = OSOMASTUNNUS_shnro;

 if ID = '' then delete;

 keep FIRM ID YEAR OSAKELKM_NUM;
 rename OSAKELKM_NUM = OSAKELKM;

run;

proc sort data = s11;
 by ID YEAR FIRM OSAKELKM;
run;

data s11_b;
 set s11; 
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM = lag(OSAKELKM) then delete;
 DUP = 0;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM ne lag(OSAKELKM) then DUP = 1;
run;

data d_11;
 set s11_b;
 if DUP = 0 then delete;
 DEL = 1;
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = d_11;			
 by FIRM YEAR;
run;

* Combines data;

proc sort data = s11_b;
 by FIRM YEAR;
run;

data s11_c;
 merge s11_b d_11;
 by FIRM YEAR;				
 if DEL = 1 then delete;				
 drop DUP DEL;
run;

proc sort nodupkey data = s11_c;
 by FIRM YEAR ID;						
run;

* Year 2012;

data s12;
 set lib24.vero6b_9_s_osakas_2012;

 YEAR = 2012;
		
 OSAKELKM_NUM = OSAKELKM*1;					
 if OSAKELKM_NUM = 0 then delete;				

 FIRM = syrtun;

 ID = OSOMASTUNNUS_shnro;

 if ID = '' then delete;

 keep FIRM ID YEAR OSAKELKM_NUM;
 rename OSAKELKM_NUM = OSAKELKM;

run;

proc sort data = s12;
 by ID YEAR FIRM OSAKELKM;
run;

data s12_b;
 set s12; 
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM = lag(OSAKELKM) then delete;
 DUP = 0;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM ne lag(OSAKELKM) then DUP = 1;
run;

data d_12;
 set s12_b;
 if DUP = 0 then delete;
 DEL = 1;
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = d_12;			
 by FIRM YEAR;
run;

* Combines data;

proc sort data = s12_b;
 by FIRM YEAR;
run;

data s12_c;
 merge s12_b d_12;
 by FIRM YEAR;				
 if DEL = 1 then delete;				
 drop DUP DEL;
run;

proc sort nodupkey data = s12_c;
 by FIRM YEAR ID;						
run;

* Year 2013;

data s13;
 set lib24.vero6b_9_s_osakas_2013;

 YEAR = 2013;
		
 OSAKELKM_NUM = OSAKELKM*1;					
 if OSAKELKM_NUM = 0 then delete;				

 FIRM = syrtun;

 ID = OSOMASTUNNUS_shnro;

 if ID = '' then delete;

 keep FIRM ID YEAR OSAKELKM_NUM;
 rename OSAKELKM_NUM = OSAKELKM;

run;

proc sort data = s13;
 by ID YEAR FIRM OSAKELKM;
run;

data s13_b;
 set s13; 
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM = lag(OSAKELKM) then delete;
 DUP = 0;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM ne lag(OSAKELKM) then DUP = 1;
run;

data d_13;
 set s13_b;
 if DUP = 0 then delete;
 DEL = 1;
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = d_13;			
 by FIRM YEAR;
run;

* Combines data;

proc sort data = s13_b;
 by FIRM YEAR;
run;

data s13_c;
 merge s13_b d_13;
 by FIRM YEAR;				
 if DEL = 1 then delete;				
 drop DUP DEL;
run;

proc sort nodupkey data = s13_c;
 by FIRM YEAR ID;						
run;

* Year 2014;

data s14;
 set lib24.vero6b_9_s_osakas_2014;

 YEAR = 2014;
		
 OSAKELKM_NUM = OSAKELKM*1;					
 if OSAKELKM_NUM = 0 then delete;				

 FIRM = syrtun;

 ID = OSOMASTUNNUS_shnro;

 if ID = '' then delete;

 keep FIRM ID YEAR OSAKELKM_NUM;
 rename OSAKELKM_NUM = OSAKELKM;

run;

proc sort data = s14;
 by ID YEAR FIRM OSAKELKM;
run;

data s14_b;
 set s14; 
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM = lag(OSAKELKM) then delete;
 DUP = 0;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM ne lag(OSAKELKM) then DUP = 1;
run;

data d_14;
 set s14_b;
 if DUP = 0 then delete;
 DEL = 1;
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = d_14;			
 by FIRM YEAR;
run;

* Combines data;

proc sort data = s14_b;
 by FIRM YEAR;
run;

data s14_c;
 merge s14_b d_14;
 by FIRM YEAR;				
 if DEL = 1 then delete;				
 drop DUP DEL;
run;

proc sort nodupkey data = s14_c;
 by FIRM YEAR ID;						
run;

* Year 2015;

data s15;
 set lib24.vero6b_9_s_osakas_2015;

 YEAR = 2015;
		
 OSAKELKM_NUM = OSAKELKM*1;					
 if OSAKELKM_NUM = 0 then delete;				

 FIRM = syrtun;

 ID = OSOMASTUNNUS_shnro;

 if ID = '' then delete;

 keep FIRM ID YEAR OSAKELKM_NUM;
 rename OSAKELKM_NUM = OSAKELKM;

run;

proc sort data = s15;
 by ID YEAR FIRM OSAKELKM;
run;

data s15_b;
 set s15; 
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM = lag(OSAKELKM) then delete;
 DUP = 0;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM ne lag(OSAKELKM) then DUP = 1;
run;

data d_15;
 set s15_b;
 if DUP = 0 then delete;
 DEL = 1;
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = d_15;			
 by FIRM YEAR;
run;

* Combines data;

proc sort data = s15_b;
 by FIRM YEAR;
run;

data s15_c;
 merge s15_b d_15;
 by FIRM YEAR;				
 if DEL = 1 then delete;				
 drop DUP DEL;
run;

proc sort nodupkey data = s15_c;
 by FIRM YEAR ID;						
run;

* Year 2016;

data s16;
 set lib24.vero6b_9_s_osakas_2016;

 YEAR = 2016;
		
 OSAKELKM_NUM = OSAKELKM*1;					
 if OSAKELKM_NUM = 0 then delete;				

 FIRM = syrtun;

 ID = OSOMASTUNNUS_shnro;

 if ID = '' then delete;

 keep FIRM ID YEAR OSAKELKM_NUM;
 rename OSAKELKM_NUM = OSAKELKM;

run;

proc sort data = s16;
 by ID YEAR FIRM OSAKELKM;
run;

data s16_b;
 set s16; 
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM = lag(OSAKELKM) then delete;
 DUP = 0;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM ne lag(OSAKELKM) then DUP = 1;
run;

data d_16;
 set s16_b;
 if DUP = 0 then delete;
 DEL = 1;
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = d_16;			
 by FIRM YEAR;
run;

* Combines data;

proc sort data = s16_b;
 by FIRM YEAR;
run;

data s16_c;
 merge s16_b d_16;
 by FIRM YEAR;				
 if DEL = 1 then delete;				
 drop DUP DEL;
run;

proc sort nodupkey data = s16_c;
 by FIRM YEAR ID;						
run;

* Year 2017;

data s17;
 set lib24.vero6b_9_s_osakas_2017;

 YEAR = 2017;
		
 OSAKELKM_NUM = OSAKELKM*1;					
 if OSAKELKM_NUM = 0 then delete;				

 FIRM = syrtun;

 ID = shnro;

 if ID = '' then delete;

 keep FIRM ID YEAR OSAKELKM_NUM;
 rename OSAKELKM_NUM = OSAKELKM;

run;

proc sort data = s17;
 by ID YEAR FIRM OSAKELKM;
run;

data s17_b;
 set s17; 
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM = lag(OSAKELKM) then delete;
 DUP = 0;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM ne lag(OSAKELKM) then DUP = 1;
run;

data d_17;
 set s17_b;
 if DUP = 0 then delete;
 DEL = 1;
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = d_17;			
 by FIRM YEAR;
run;

* Combines;

proc sort data = s17_b;
 by FIRM YEAR;
run;

data s17_c;
 merge s17_b d_17;
 by FIRM YEAR;				
 if DEL = 1 then delete;				
 drop DUP DEL;
run;

proc sort nodupkey data = s17_c;
 by FIRM YEAR ID;						
run;

* Year 2018;

data s18;
 set lib24.vero6b_9_s_osakas_2018;

 YEAR = 2018;
		
 OSAKELKM_NUM = OSAKELKM*1;					
 if OSAKELKM_NUM = 0 then delete;				

 FIRM = syrtun;

 ID = shnro;

 if ID = '' then delete;

 keep FIRM ID YEAR OSAKELKM_NUM;
 rename OSAKELKM_NUM = OSAKELKM;

run;

proc sort data = s18;
 by ID YEAR FIRM OSAKELKM;
run;

data s18_b;
 set s18; 
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM = lag(OSAKELKM) then delete;
 DUP = 0;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM ne lag(OSAKELKM) then DUP = 1;
run;

data d_18;
 set s18_b;
 if DUP = 0 then delete;
 DEL = 1;
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = d_18;			
 by FIRM YEAR;
run;

* Combines data;

proc sort data = s18_b;
 by FIRM YEAR;
run;

data s18_c;
 merge s18_b d_18;
 by FIRM YEAR;				
 if DEL = 1 then delete;				
 drop DUP DEL;
run;

proc sort nodupkey data = s18_c;
 by FIRM YEAR ID;						
run;

* Year 2019;

data s19;
 set lib24.vero6b_9_s_osakas_2019;

 YEAR = 2019;
		
 OSAKELKM_NUM = OSAKELKM*1;					
 if OSAKELKM_NUM = 0 then delete;				

 FIRM = syrtun;

 ID = shnro;

 if ID = '' then delete;

 keep FIRM ID YEAR OSAKELKM_NUM;
 rename OSAKELKM_NUM = OSAKELKM;

run;

proc sort data = s19;
 by ID YEAR FIRM OSAKELKM;
run;

data s19_b;
 set s19; 
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM = lag(OSAKELKM) then delete;
 DUP = 0;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) and ID = lag(ID) and OSAKELKM ne lag(OSAKELKM) then DUP = 1;
run;

data d_19;
 set s19_b;
 if DUP = 0 then delete;
 DEL = 1;
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = d_19;			
 by FIRM YEAR;
run;

* Combines;

proc sort data = s19_b;
 by FIRM YEAR;
run;

data s19_c;
 merge s19_b d_19;
 by FIRM YEAR;				
 if DEL = 1 then delete;				
 drop DUP DEL;
run;

proc sort nodupkey data = s19_c;
 by FIRM YEAR ID;						
run;

* Merge data from years 2006 - 2019;

data s;
 set s06_c s07_c s08_c s09_c s10_c s11_c s12_c s13_c s14_c s15_c s16_c s17_c s18_c s19_c;
run;

proc sort nodupkey data = s;
 by FIRM YEAR ID;						
run;

proc sort data = s;
 by FIRM YEAR;
run;

* Calculate the number of shareholders per firm and year;

data s2;
 set s;
 by FIRM YEAR;
 if first.YEAR then C=1;
 else C+1;											
run;

* Merge data on total amount of shares ;

/* YEAR 2006 */

data shares_06;
 set lib24.verolomake6b_9_s_2006;

 YEAR = 2006;

 FIRM = syrtun;

 LUKUMAARA_NUM = LUKUMAARA*1;

 keep LUKUMAARA_NUM FIRM YEAR;
 rename LUKUMAARA_NUM = LUKUMAARA;
 
run;

/* YEAR 2007 */

data shares_07;
 set lib24.verolomake6b_9_s_2007;

 YEAR = 2007;

 FIRM = syrtun;

 LUKUMAARA_NUM = LUKUMAARA*1;

 keep LUKUMAARA_NUM FIRM YEAR;
 rename LUKUMAARA_NUM = LUKUMAARA;
 
run;

/* YEAR 2008 */

data shares_08;
 set lib24.verolomake6b_9_s_2008;

 YEAR = 2008;

 FIRM = syrtun;

 LUKUMAARA_NUM = LUKUMAARA*1;

 keep LUKUMAARA_NUM FIRM YEAR;
 rename LUKUMAARA_NUM = LUKUMAARA;
 
run;

/* YEAR 2009 */

data shares_09;
 set lib24.verolomake6b_9_s_2009;

 YEAR = 2009;

 FIRM = syrtun;

 LUKUMAARA_NUM = LUKUMAARA*1;

 keep LUKUMAARA_NUM FIRM YEAR;
 rename LUKUMAARA_NUM = LUKUMAARA;
 
run;

/* YEAR 2010 */

data shares_10;
 set lib24.verolomake6b_9_s_2010;

 YEAR = 2010;

 FIRM = syrtun;

 LUKUMAARA_NUM = LUKUMAARA*1;

 keep LUKUMAARA_NUM FIRM YEAR;
 rename LUKUMAARA_NUM = LUKUMAARA;
 
run;

/* YEAR 2011 */

data shares_11;
 set lib24.verolomake6b_9_s_2011;

 YEAR = 2011;

 FIRM = syrtun;

 LUKUMAARA_NUM = LUKUMAARA*1;

 keep LUKUMAARA_NUM FIRM YEAR;
 rename LUKUMAARA_NUM = LUKUMAARA;
 
run;

/* YEAR 2012 */

data shares_12;
 set lib24.verolomake6b_9_s_2012;

 YEAR = 2012;

 FIRM = syrtun;

 LUKUMAARA_NUM = LUKUMAARA*1;

 keep LUKUMAARA_NUM FIRM YEAR;
 rename LUKUMAARA_NUM = LUKUMAARA;
 
run;

/* YEAR 2013 */

data shares_13;
 set lib24.verolomake6b_9_s_2013;

 YEAR = 2013;

 FIRM = syrtun;

 LUKUMAARA_NUM = osakkeidenlkm*1;

 keep LUKUMAARA_NUM FIRM YEAR;
 rename LUKUMAARA_NUM = LUKUMAARA;
 
run;

/* YEAR 2014 */

data shares_14;
 set lib24.verolomake6b_9_s_2014;

 YEAR = 2014;

 FIRM = syrtun;

 LUKUMAARA_NUM = osakkeidenlkm*1;

 keep LUKUMAARA_NUM FIRM YEAR;
 rename LUKUMAARA_NUM = LUKUMAARA;
 
run;

/* YEAR 2015 */

data shares_15;
 set lib24.verolomake6b_9_s_2015;

 YEAR = 2015;

 FIRM = syrtun;

 LUKUMAARA_NUM = osakkeidenlkm*1;

 keep LUKUMAARA_NUM FIRM YEAR;
 rename LUKUMAARA_NUM = LUKUMAARA;
 
run;

/* YEAR 2016 */

data shares_16;
 set lib24.verolomake6b_9_s_2016;

 YEAR = 2016;

 FIRM = syrtun;

 LUKUMAARA_NUM = osakkeidenlkm*1;

 keep LUKUMAARA_NUM FIRM YEAR;
 rename LUKUMAARA_NUM = LUKUMAARA;
 
run;

/* YEAR 2017 */

data shares_17;
 set lib24.vero6b_osakkeet_prh_asiakas_2017;

 YEAR = 2017;

 FIRM = syrtun;

 LUKUMAARA_NUM = osakkeidenlkm*1;

 keep LUKUMAARA_NUM FIRM YEAR;
 rename LUKUMAARA_NUM = LUKUMAARA;
 
run;

/* YEAR 2018 */

data shares_18;
 set lib24.vero6b_osakkeet_9_s_2018;

 YEAR = 2018;

 FIRM = syrtun;

 LUKUMAARA_NUM = osakkeidenlkm*1;

 keep LUKUMAARA_NUM FIRM YEAR;
 rename LUKUMAARA_NUM = LUKUMAARA;
 
run;

/* YEAR 2019 */

data shares_19;
 set lib24.vero6b_osakkeet_9_s_2019;

 YEAR = 2019;

 FIRM = syrtun;

 LUKUMAARA_NUM = osakkeidenlkm*1;

 keep LUKUMAARA_NUM FIRM YEAR;
 rename LUKUMAARA_NUM = LUKUMAARA;

run;

data totshares;
set shares_06 shares_07 shares_08 shares_09 shares_10 shares_11 shares_12 shares_13 shares_14 shares_15 shares_16 shares_17 shares_18 shares_19;

if LUKUMAARA = 0 then delete;

run;

proc sort nodupkey data = totshares;
by FIRM YEAR;
run;

data s3;
 merge s2 totshares;
 by FIRM YEAR;
 if LUKUMAARA = . then delete; 				
 if OSAKELKM = . then delete;
 DIFF = LUKUMAARA - OSAKELKM;
 PERCENTAGEOWN = OSAKELKM / LUKUMAARA;		* %-ownership per owner and year;
run;

proc sort nodupkey data = s2;
 by FIRM YEAR ID;				
run;

data correct;
 set s3;
 DEL = 0;
 if PERCENTAGEOWN > 1 then DEL = 1;		
 if DEL = 0 then delete; 
 keep FIRM YEAR DEL;
run;

proc sort nodupkey data = correct;
 by FIRM YEAR;
run;

/* Merge */

proc sort data = s2;
 by FIRM YEAR;
run;

data s4;
 merge s3 correct;
 by FIRM YEAR;
 if DEL = 1 then delete; 				
 if ID = ' ' then delete;
 drop DEL DIFF;
run;

* Create variables to indicate the number of shareholders in a given year;

data maximum;
 set s4;
 keep FIRM YEAR C;
run;

proc sort data = maximum;
 by FIRM YEAR descending C;				
run; 

data maximum2;		
 set maximum;
 if FIRM = lag(FIRM) and YEAR = lag(YEAR) then delete;		 
 NUMOWNERS = C;						
 keep FIRM YEAR NUMOWNERS;  
run;

proc sort nodupkey data = maximum2;
 by FIRM YEAR;
run;

/* Merge */

proc sort data = s4;
 by FIRM YEAR;
run;

data s5;
 merge s4 maximum2;
 by FIRM YEAR;
 drop C; 
run;

proc sort data = s5;
 by FIRM YEAR ID;
run;


/* Calculate total ownership percentage per firm-year */

proc sort data = s5;
 by FIRM YEAR;
run;

proc means noprint data = s5;
 var PERCENTAGEOWN; 
 by FIRM YEAR;
 output out = totalownership
 sum = SUMPERCENTAGEOWN;
run;

data totalownership2;
 set totalownership;
 if SUMPERCENTAGEOWN > 1 then delete;			* The sum of all ownerships cannot be greater than 100%;
 if SUMPERCENTAGEOWN < 0.1 then delete;			* In tax filings, the sum of ownership cannot be less than 10%;
 drop _TYPE_;
 drop _FREQ_;
run;

/* Merge to main data */

proc sort nodupkey data = totalownership2;
 by FIRM YEAR;
run;

proc sort data = s5;
 by FIRM YEAR;
run;

data s6;
 merge s5 totalownership2;
 by FIRM YEAR;
 if SUMPERCENTAGEOWN = . then delete;
run;

* Saves data;

data lib100.Osakkeenomistajat_data_230925;
 set s6;
run;


