**************************
****education investment**
**************************
global susenas "E:\Data research\Data\All Susenas"



*cleaning
use "$susenas/Susenas 2019/susenas19mar_ki", clear

/*=========rata-rata lama sekolah============*/
ren r612 partischool
ren r613 levschool
ren r614 claschool
ren r615 ijazah
ren r407 umur

recode levschool (01 02 03 04=1) (05 06 07 08=2) (09 10 11 12 13 14=3) (15 16 17=4) (18=5) (19 20 21=6)
recode ijazah (01=0)(02 03 04 05=1) (06 07 08 09=2) (10 11 12 13 14 15=3) (16 17 18=4) (19=5) (20 21 22=6)
*recode claschool (1 2=1) (3 4=2) (5 6 7=3)

gen yearschool=.

/*tidak sekolah*/
replace yearschool=0 if partischool==1 & ijazah==0
replace yearschool=0 if partischool==1 & ijazah==. /*yang blm pernah sekolah dan data ijazah missing*/
/*sd*/
replace yearschool=claschool-1 if partischool==2 & ijazah==0 & claschool!=8
replace yearschool=claschool-1 if partischool==3 & ijazah==0 & claschool!=8
replace yearschool=6 if partischool==3 & ijazah==1 & claschool==8
replace yearschool=0 if partischool==3 & ijazah==0 & claschool==8 /*tes*/
replace yearschool=6+claschool-1 if partischool==3 & ijazah==1 & claschool!=8
replace yearschool=6+claschool-1 if partischool==2 & ijazah==1 & claschool!=8
/*smp*/
replace yearschool=9 if partischool==3 & ijazah==2 & claschool==8
replace yearschool=9+claschool-1 if partischool==3 & ijazah==2 & claschool!=8/*asumsi DO*/
replace yearschool=9+claschool-1 if partischool==2 & ijazah==2 & claschool!=8
/*sma*/
replace yearschool=12 if partischool==3 & ijazah==3 & claschool==8
replace yearschool=12+claschool-1 if partischool==3 & ijazah==3 & claschool!=8
replace yearschool=12+claschool-1 if partischool==2 & ijazah==3 & claschool!=8
/*d3*/
replace yearschool=15 if partischool==3 & ijazah==4 & claschool==8
replace yearschool=15+claschool-1 if partischool==3 & ijazah==4 & claschool!=8
replace yearschool=15+claschool-1 if partischool==2 & ijazah==4 & claschool!=8
/*s1*/
replace yearschool=16 if partischool==3 & ijazah==5 & claschool==8
replace yearschool=16+claschool-1 if partischool==3 & ijazah==5 & claschool!=8
replace yearschool=16+claschool-1 if partischool==2 & ijazah==5 & claschool!=8
/*s2 s3*/
replace yearschool=18 if partischool==3 & ijazah==6 & claschool==8
replace yearschool=18+claschool-1 if partischool==3 & ijazah==6 & claschool!=8
replace yearschool=18+claschool-1 if partischool==2 & ijazah==6 & claschool!=8

keep if yearschool!=.

gen jenjang=1 if year==12
replace jenjang=2 if year==15
replace jenjang=3 if year==18
replace jenjang=4 if year>18

*bekerja
gen bekerja=1 if r701_a!=""
replace bekerja=1 if r703==1

keep if bekerja==1

gen lama_bekerja=umur-yearschool-6
gen lama_bekerja2=lama_bekerja^2


*pendapatan
preserve
use "$susenas/Susenas 2019/susenas19mar_kr", clear
ren *, lower
keep exp_cap urut
tempfile rt
	save `rt'
restore

merge m:1 urut using `rt'
keep if _merge==3
drop _m

gen ln_exp=ln(exp_cap)
gen iweight=int(fwt)

*independen
gen urban=(r105==1)
gen male=(r405==1)

*independen in district level
preserve
use "$susenas/Susenas 2019/susenas19mar_ki", clear
gen penduduk=1
gen iweight=int(fwt)
gen r102_ = string(r102,"%02.0f")
drop r102
ren r102_ r102

tostring r101, replace
gen id_kabup=r101+r102
collapse (sum) penduduk, by(id_kabup)
tempfile dis
	save `dis'
restore

gen r102_ = string(r102,"%02.0f")
drop r102
ren r102_ r102

tostring r101, replace
gen id_kabup=r101+r102
merge m:1 id_kabup using `dis'

destring id_kabup, replace

drop _merge
merge m:1 id_kabup using "$susenas/list kabupaten rev"

0
*regresi
reg ln_exp yearschool lama_bekerja lama_bekerja2 urban male penduduk
reg ln_exp primary lama_bekerja lama_bekerja2 urban male penduduk if yearschool<=12
reg ln_exp highschool lama_bekerja lama_bekerja2 urban male penduduk if yearschool<=12

gen kota_admin=(strpos(KABKOT,"KOTA "))

reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if kota_admin==1
reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if kota_admin==0

reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if r105==1
reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if r105==2

xtile quantile=exp_cap, nq(5)

reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if r105==1 & inlist(quantile,4,5)
reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if r105==1 & inlist(quantile,1,2) //dihasil ini mungkin pendidikan rendah kalah bersaing
reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if r105==2 & inlist(quantile,4,5)
reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if r105==2 & inlist(quantile,1,2)

reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if quantile==1 | quantile==2
reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if quantile==4 | quantile==5

gen miskin=(exp_cap<425250)
reg ln_exp yearschool lama_bekerja lama_bekerja2 urban male penduduk if miskin==0
reg ln_exp yearschool lama_bekerja lama_bekerja2 urban male penduduk if miskin==1

		*final yang digunakan
eststo _1:reg ln_exp yearschool lama_bekerja lama_bekerja2 urban kota_admin male penduduk, robust
eststo _2:reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if r105==1, robust
eststo _3:reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if r105==2, robust
eststo _4:reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if r105==1 & inlist(quantile,4,5), robust
eststo _5:reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if r105==1 & inlist(quantile,1,2), robust
eststo _6:reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if r105==2 & inlist(quantile,4,5), robust
eststo _7:reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if r105==2 & inlist(quantile,1,2), robust
eststo _8:reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if kota_admin==1, robust
eststo _9:reg ln_exp yearschool lama_bekerja lama_bekerja2 male penduduk if kota_admin==0, robust

outreg2 [_1 _2 _3 _4 _5 _6 _7 _8 _9] using "C:\Users\YusufFM\Desktop\2020\KPP UI - Riset\Paper - education investment\output kuantitatif\linear_reg2", excel replace

*membuat hasil regresi excel untuk 514 kab kota
reg ln_exp yearschool lama_bekerja lama_bekerja2 urban male penduduk, robust
reg ln_exp yearschool lama_bekerja lama_bekerja2 urban male penduduk [fw=iweight]

encode id_kabup, gen(id_kabup1)

/*
foreach var in 1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59	60	61	62	63	64	65	66	67	68	69	70	71	72	73	74	75	76	77	78	79	80	81	82	83	84	85	86	87	88	89	90	91	92	93	94	95	96	97	98	99	100	101	102	103	104	105	106	107	108	109	110	111	112	113	114	115	116	117	118	119	120	121	122	123	124	125	126	127	128	129	130	131	132	133	134	135	136	137	138	139	140	141	142	143	144	145	146	147	148	149	150	151	152	153	154	155	156	157	158	159	160	161	162	163	164	165	166	167	168	169	170	171	172	173	174	175	176	177	178	179	180	181	182	183	184	185	186	187	188	189	190	191	192	193	194	195	196	197	198	199	200	201	202	203	204	205	206	207	208	209	210	211	212	213	214	215	216	217	218	219	220	221	222	223	224	225	226	227	228	229	230	231	232	233	234	235	236	237	238	239	240	241	242	243	244	245	246	247	248	249	250	251	252	253	254	255	256	257	258	259	260	261	262	263	264	265	266	267	268	269	270	271	272	273	274	275	276	277	278	279	280	281	282	283	284	285	286	287	288	289	290	291	292	293	294	295	296	297	298	299	300{
eststo _`var': qui reg ln_exp yearschool lama_bekerja lama_bekerja2 urban male penduduk if id_kabup1==`var', robust
}

	outreg2 [_1	_2	_3	_4	_5	_6	_7	_8	_9	_10	_11	_12	_13	_14	_15	_16	_17	_18	_19	_20	_21	_22	_23	_24	_25	_26	_27	_28	_29	_30	_31	_32	_33	_34	_35	_36	_37	_38	_39	_40	_41	_42	_43	_44	_45	_46	_47	_48	_49	_50	_51	_52	_53	_54	_55	_56	_57	_58	_59	_60	_61	_62	_63	_64	_65	_66	_67	_68	_69	_70	_71	_72	_73	_74	_75	_76	_77	_78	_79	_80	_81	_82	_83	_84	_85	_86	_87	_88	_89	_90	_91	_92	_93	_94	_95	_96	_97	_98	_99	_100	_101	_102	_103	_104	_105	_106	_107	_108	_109	_110	_111	_112	_113	_114	_115	_116	_117	_118	_119	_120	_121	_122	_123	_124	_125	_126	_127	_128	_129	_130	_131	_132	_133	_134	_135	_136	_137	_138	_139	_140	_141	_142	_143	_144	_145	_146	_147	_148	_149	_150	_151	_152	_153	_154	_155	_156	_157	_158	_159	_160	_161	_162	_163	_164	_165	_166	_167	_168	_169	_170	_171	_172	_173	_174	_175	_176	_177	_178	_179	_180	_181	_182	_183	_184	_185	_186	_187	_188	_189	_190	_191	_192	_193	_194	_195	_196	_197	_198	_199	_200	_201	_202	_203	_204	_205	_206	_207	_208	_209	_210	_211	_212	_213	_214	_215	_216	_217	_218	_219	_220	_221	_222	_223	_224	_225	_226	_227	_228	_229	_230	_231	_232	_233	_234	_235	_236	_237	_238	_239	_240	_241	_242	_243	_244	_245	_246	_247	_248	_249	_250	_251	_252	_253	_254	_255	_256	_257	_258	_259	_260	_261	_262	_263	_264	_265	_266	_267	_268	_269	_270	_271	_272	_273	_274	_275	_276	_277	_278	_279	_280	_281	_282	_283	_284	_285	_286	_287	_288	_289	_290	_291	_292	_293	_294	_295	_296	_297	_298	_299	_300 ///
	] using "C:\Users\YusufFM\Desktop\2020\KPP UI - Riset\Paper - education investment\1-300", excel replace
*/

/*
foreach var in 301	302	303	304	305	306	307	308	309	310	311	312	313	314	315	316	317	318	319	320	321	322	323	324	325	326	327	328	329	330	331	332	333	334	335	336	337	338	339	340	341	342	343	344	345	346	347	348	349	350	351	352	353	354	355	356	357	358	359	360	361	362	363	364	365	366	367	368	369	370	371	372	373	374	375	376	377	378	379	380	381	382	383	384	385	386	387	388	389	390	391	392	393	394	395	396	397	398	399	400	401	402	403	404	405	406	407	408	409	410	411	412	413	414	415	416	417	418	419	420	421	422	423	424	425	426	427	428	429	430	431	432	433	434	435	436	437	438	439	440	441	442	443	444	445	446	447	448	449	450	451	452	453	454	455	456	457	458	459	460	461	462	463	464	465	466	467	468	469	470	471	472	473	474	475	476	477	478	479	480	481	482	483	484	485	486	487	488	489	490	491	492	493	494	495	496	497	498	499	500	501	502	503	504	505	506	507	508	509	510	511	512	513	514{
eststo _`var': qui reg ln_exp yearschool lama_bekerja lama_bekerja2 urban male penduduk if id_kabup1==`var', robust
}

	outreg2 [_301	_302	_303	_304	_305	_306	_307	_308	_309	_310	_311	_312	_313	_314	_315	_316	_317	_318	_319	_320	_321	_322	_323	_324	_325	_326	_327	_328	_329	_330	_331	_332	_333	_334	_335	_336	_337	_338	_339	_340	_341	_342	_343	_344	_345	_346	_347	_348	_349	_350	_351	_352	_353	_354	_355	_356	_357	_358	_359	_360	_361	_362	_363	_364	_365	_366	_367	_368	_369	_370	_371	_372	_373	_374	_375	_376	_377	_378	_379	_380	_381	_382	_383	_384	_385	_386	_387	_388	_389	_390	_391	_392	_393	_394	_395	_396	_397	_398	_399	_400	_401	_402	_403	_404	_405	_406	_407	_408	_409	_410	_411	_412	_413	_414	_415	_416	_417	_418	_419	_420	_421	_422	_423	_424	_425	_426	_427	_428	_429	_430	_431	_432	_433	_434	_435	_436	_437	_438	_439	_440	_441	_442	_443	_444	_445	_446	_447	_448	_449	_450	_451	_452	_453	_454	_455	_456	_457	_458	_459	_460	_461	_462	_463	_464	_465	_466	_467	_468	_469	_470	_471	_472	_473	_474	_475	_476	_477	_478	_479	_480	_481	_482	_483	_484	_485	_486	_487	_488	_489	_490	_491	_492	_493	_494	_495	_496	_497	_498	_499	_500	_501	_502	_503	_504	_505	_506	_507	_508	_509	_510	_511	_512	_513	_514  ///
	] using "C:\Users\YusufFM\Desktop\2020\KPP UI - Riset\Paper - education investment\301-514", excel replace

*/


*spatial analysis
use "C:\Users\YusufFM\Desktop\2020\KPP UI - Riset\Paper - education investment\output kuantitatif\control var1", clear
encode id_kabup, gen(id_kabup1)

gen rasio_bekerja=bekerja/penduduk
gen rasio_disabilitas=disability/penduduk
gen rasio_elc=yes_elc/(yes_elc+no_elc)
gen rasio_jamkes=jamkes/penduduk

preserve
import excel "C:\Users\YusufFM\Desktop\2020\KPP UI - Riset\Paper - education investment\output kuantitatif\education return - kabkota.xlsx", sheet("Data") firstrow clear
tempfile educ_return
	save `educ_return'
restore

merge 1:1 id_kabup1 using `educ_return'
drop _m

destring edu_return1, replace 

drop ln_exp_dis
gen ln_exp=ln(exp_cap)

reg ln_exp edu_return1 rasio_bekerja rasio_disabilitas penduduk rasio_elc rasio_jamkes, robust
tempfile raw
	save `raw'

		use "E:\Data research\Data\GIS data\Batas Indonesia\indod2.dta", clear
		keep NAME_2 TYPE_2 id CC_2
		ren NAME_2 kabup
		ren CC_2 id_kabup
		duplicates drop id_kabup, force
				
		merge 1:1 id_kabup using `raw'
		keep if _merge==3
		drop if id==477

		spset id
		cd "E:\Data research\Data\GIS data\Batas Indonesia"
		spset, modify shpfile(indocoor2)
		spmatrix create contiguity Y, replace

reg ln_exp edu_return1 rasio_bekerja rasio_disabilitas penduduk rasio_elc rasio_jamkes, robust
estat moran, errorlag(Y)

		spregress ln_exp edu_return1 rasio_bekerja rasio_disabilitas penduduk rasio_elc rasio_jamkes, gs2sls

		
		spregress ln_exp edu_return1 rasio_bekerja rasio_disabilitas penduduk rasio_elc rasio_jamkes, ivarlag(Y: edu_return1 rasio_bekerja rasio_disabilitas penduduk rasio_elc rasio_jamkes) gs2sls
		
				*salah satu masalahnya adalah push and pull effect dimana gaji daerah lain lebih tinggi dengan tingkat yang sama, maka akan ada dampak negatif seperti braindrain,, short term negatif kalo gaada kebijakan, duit tidak dibawa kedaerah asal jika tidak ada keharusan, dll (baca pdf alasan dampak buruk migrasi)

		
		/*
		grmap edu_return1 if edu_return1>=0 & strpos(edu_return,"*"), title("Rate of return on education in Indonesia year 2019")
		graph export "C:\Users\YusufFM\Desktop\2020\KPP UI - Riset\Paper - education investment\output kuantitatif\education return indonesia.png", as(png) name("Graph")
		*/
		
preserve
import excel "E:\Data research\Data\All Susenas\list kabkota metropolitan.xlsx", sheet("Sheet1") firstrow clear
ren *, lower
tostring id_kabup, replace
keep if kabkot!=""
tempfile metro
	save `metro'
restore

drop _merge
merge 1:1 id_kabup using `metro'
keep if _merge==3
		
			*LOCAL SPILLOVER GRAPH
				preserve
				predict y0

*				replace edu_return1 = 0.1 if strpos(kabkot,"JAKARTA")


*				replace edu_return1 = 0.1 if kabkot=="KOTA MEDAN"

*				replace edu_return1 = 0.1 if kabkot=="KOTA BANJARMASIN"


*				replace edu_return1 = 0.1 if kabkot=="KOTA MAKASSAR" //karena rate of return sudah persentase, maka bukan perubahan 1% dari yang ada melainkan penambahan bener2 1%

				predict y1

				gen y0_n=exp(y0)
				gen y1_n=exp(y1)

				generate y_diff = y1 - y0
				generate y_diff_n = y1_n - y0_n
/*				
				preserve
				keep if y_diff!=0				
				egen y_diff_jkt=mean(y_diff) if strpos(kabkot,"JAKARTA")
				replace y_diff=y_diff_jkt if y_diff_jkt!=.
				grmap y_diff, fcolor(Blues) clmethod(unique) 
				restore
*/

				preserve
*				keep if metropolitancenter=="Kota Medan"				//ada yang tidak terdampak, kabupaten karo dan kota binjai
*				keep if metropolitancenter=="Kota Makassar"			//terdampak seluruhnya
*				keep if metropolitancenter=="Kota Banjarmasin"  //ada yang tidak terdampak, kota banjar baru dan tanah laut
				grmap y_diff, fcolor(Blues) clmethod(unique)  
				restore


				grmap y_diff, title("edu return") fcolor(Blues) clmethod(unique) t(2019) label(data(grmaplabels1) xcoord(_CX) ycoord(_CY) label(NAME_2) color(black) size(*0.5))
				graph export "C:\Users\YusufFM\Desktop\2020\KPP UI - Riset\Paper - disability\local_apsjatim.png", as(png) name("Graph")
				
	*			grmap y_diff_rasiobekerjadis, title("Local spillover") fcolor(Blues) clmethod(unique) t(2019) label(data(grmaplabels1) xcoord(_CX) ycoord(_CY) label(NAME_2) color(black) size(*0.5))
	*			graph save "Graph" "C:\Users\YusufFM\Desktop\2020\KPP UI - Riset\Paper - disability\Global spillover_rasiobekerjadis.gph", replace
	
				grmap y_diff_highjamkesdis, title("Local spillover") fcolor(Blues) clmethod(unique) t(2019) label(data(grmaplabels1) xcoord(_CX) ycoord(_CY) label(NAME_2) color(black) size(*0.5))		
				graph export "C:\Users\YusufFM\Desktop\2020\KPP UI - Riset\Paper - disability\local_jamkesdisjatim.png", as(png) name("Graph")

				grmap y_diff_highrasiorsdis, title("Local spillover") fcolor(Blues) clmethod(unique) t(2019) label(data(grmaplabels1) xcoord(_CX) ycoord(_CY) label(NAME_2) color(black) size(*0.5))		
				graph export "C:\Users\YusufFM\Desktop\2020\KPP UI - Riset\Paper - disability\local_rasiorsjatim.png", as(png) name("Graph")


	restore
*/