VAT_credit/load_data_sum.do at main · mhazizian/VAT_credit · GitHub

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


********************************
// Reshape data
keep address category is_urban tv_* cat_*
duplicates drop

reshape wide cat_*, i(address) j(category)
order address is_urban tv_* cat_*


local cats 13 1 3 4 5 6 7 8 9 10 11 12
foreach cat of local cats {
    replace cat_exm_`cat' 	= 0 if missing(cat_exm_`cat')
	replace cat_9_`cat' 	= 0 if missing(cat_9_`cat')
	replace cat_other_`cat' = 0 if missing(cat_other_`cat')
}


tempfile expenditure_tempfile
save `expenditure_tempfile', replace


********************************
// import data summary

clear
tempfile sum_tempfile
save `sum_tempfile', replace empty

foreach RU in "U" "R" {
	import excel "$dir_sum/Sum`RU'${year}.xls", firstrow clear //

	// rename ADDRESS address
	foreach v in ADDRESS Address {
	   capture confirm var `v'
	   if _rc == 0  {
				rename `v' address
	   }
	}
	destring address, replace
	destring B24, replace

	append using `sum_tempfile'
	save `sum_tempfile', replace
}

merge 1:1 address using `expenditure_tempfile'

drop if _merge == 1
drop _merge

order address is_urban tv_* cat_* *

replace C09New = C09New + 1
gen ln_NHazine = ln(NHazine)
gen ln_GHazine = ln(GHazine)
// replace weight = int(weight)

*******************************
// Ratio calculation

gen calculated_exp 		= (tv_exm + tv_9 + tv_other)
gen cal_exp_to_SC_exp 	= calculated_exp / NHazine
gen cal_exp_to_SC_exp_G = calculated_exp / GHazine

gen ratio_tv_9   = tv_9 / calculated_exp
gen ratio_tv_exm = tv_exm / calculated_exp

gen ratio_tv_exm_SC 	= tv_exm / NHazineh
gen ratio_tv_9_SC 		= tv_9 / NHazineh
gen ratio_tv_other_SC 	= tv_other / NHazineh

replace ratio_tv_exm_SC	= . if ratio_tv_exm_SC < 0 | ratio_tv_exm_SC > 1
replace ratio_tv_9_SC	= . if ratio_tv_9_SC < 0 | ratio_tv_9_SC > 1
replace ratio_tv_other_SC	= . if ratio_tv_other_SC < 0 | ratio_tv_other_SC > 1


gen tax_exp = tv_exm * 0.09
gen tax_exp_MT = tax_exp / 10 / 1000 / 1000

********************************
// Ratios in category

local cats 13 1 3 4 5 6 7 8 9 10 11 12
foreach cat of local cats {
    gen ratio_cat_9_SC_`cat' = cat_9_`cat' / NHazineh
    gen ratio_cat_exm_SC_`cat' = cat_exm_`cat' / NHazineh

    gen ratio_cat_9_`cat' = cat_9_`cat' / calculated_exp
    gen ratio_cat_exm_`cat' = cat_exm_`cat' / calculated_exp

	gen share_cat_9_`cat' = cat_9_`cat' / tv_9
	gen share_cat_exm_`cat' = cat_exm_`cat' / tv_exm


	replace ratio_cat_9_`cat' 		= . if ratio_cat_9_`cat' < 0 		| ratio_cat_9_`cat' > 1
	replace ratio_cat_9_SC_`cat' 	= . if ratio_cat_9_SC_`cat' < 0 	| ratio_cat_9_SC_`cat' > 1
	replace ratio_cat_exm_`cat' 	= . if ratio_cat_exm_`cat' < 0 		| ratio_cat_exm_`cat' > 1
	replace ratio_cat_exm_SC_`cat' 	= . if ratio_cat_exm_SC_`cat' < 0 	| ratio_cat_exm_SC_`cat' > 1
}