Panel - Loop collapse or egen with condition

[Waewnet Sukkasem]

Dear all,

I googled and tried many command from yesterday and until now it's still unsuccessful. Could you please expert help me on this? Thank you very much in advance.

My data is like this --->

6 types
2 years : 18-19, 19-20
many districts

type	year	district	budget	Q1	Q2	Q3
DS	18-19	Love	10	8	1	1
DS	19-20	Love	6	2	2	4
EDU	18-19	Chiniot	5	1	1	1
EDU	19-20	Chiniot	4	3	3	3
HLT	18-19	Chiniot	10	2	2	2
HLT	19-20	Chiniot	8	1	1	1
EDU	18-19	Jhang	4	4	4	4
EDU	19-20	Jhang	2	2	2	2
HLT	18-19	Jhang	3	3	3	3
HLT	19-20	Jhang	1	1	1	1
DS	19-19	Cute	10	3	3	3
DS	19-20	Cute	2	1	1	1
HLT	18-19	Harnai	20	10	10	10
DS	18-19	Harnai	4	4	4	4
HLT	19-20	Sibi	2	2	2	2
DS	18-19	Sibi	5	2	2	3

.

1) I want to clone everything if var district aren't these name
"Chiniot", "Jhang", "Harnai","Sibi", "Lakki" "Bannu" "Nushki" "Chagai"

2) But if the districts name above: I want to collapse (sum) by each type (DS sum with DS) and each year (18-19 with 19-20) with condition below

sum of "Chiniot", "Jhang", ------> and using district name "Chiniot"
sum of "Harnai","Sibi", ------> and using district name "Harnai"
sum of "Lakki" "Bannu" ------> and using district name "Lakki"
sum of "Nushki" "Chagai" ------> and using district name "Nushki"





The result that I expect would be

ype	year	district	budget	Q1	Q2	Q3
DS	18-19	Love	10	8	1	1
DS	19-20	Love	6	2	2	4
EDU	18-19	Chiniot	9	5	5	5
EDU	19-20	Chiniot	6	5	5	5
HLT	18-19	Chiniot	13	5	5	5
HLT	19-20	Chiniot	9	2	2	2
DS	19-19	Cute	10	3	3	3
DS	19-20	Cute	2	1	1	1
HLT	18-19	Harnai	20	10	10	10
DS	18-19	Harnai	4	4	4	4
HLT	19-20	Sibi	9	6	6	7

.

All experts in STATA: Could you please help me with this?
I tried foreach, collapse, egen, gen, clonevar, but it didn't work.

Thank you very much.

[Clyde Schechter]

I think I have the code you want, but it does not exactly reproduce the results you show. I think the last row of your table is an error: there should be no row for Sibi: it should be for Harnai, and the numbers are also wrong.

The trick is to split the data set into two pieces. The first contains only those observations you want to "clone" --and you do nothing with them but put them aside for later use. The second piece contains the other ones. Then you change the names of the districts to match the scheme you want in the results, and then use the -collapse- command to add things up.

The final step is to put the two pieces of the data set back together with -append-.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str3 type str5 year str7 district byte(budget q1 q2 q3)
"DS"  "18-19" "Love"    10  8  1  1
"DS"  "19-20" "Love"     6  2  2  4
"EDU" "18-19" "Chiniot"  5  1  1  1
"EDU" "19-20" "Chiniot"  4  3  3  3
"HLT" "18-19" "Chiniot" 10  2  2  2
"HLT" "19-20" "Chiniot"  8  1  1  1
"EDU" "18-19" "Jhang"    4  4  4  4
"EDU" "19-20" "Jhang"    2  2  2  2
"HLT" "18-19" "Jhang"    3  3  3  3
"HLT" "19-20" "Jhang"    1  1  1  1
"DS"  "19-19" "Cute"    10  3  3  3
"DS"  "19-20" "Cute"     2  1  1  1
"HLT" "18-19" "Harnai"  20 10 10 10
"DS"  "18-19" "Harnai"   4  4  4  4
"HLT" "19-20" "Sibi"     2  2  2  2
"DS"  "18-19" "Sibi"     5  2  2  3
end

preserve
keep if !inlist(district, "Chiniot", "Jhang", "Harnai","Sibi", "Lakki" "Bannu" "Nushki" "Chagai")
tempfile clones
save `clones'

restore
keep if inlist(district, "Chiniot", "Jhang", "Harnai","Sibi", "Lakki" "Bannu" "Nushki" "Chagai")
replace district = "Chiniot" if district == "Jhang"
replace district = "Harnai" if district == "Sibi"
replace district = "Lakki" if district == "Bannu"
replace district = "Nushki" if district == "Chagai"

collapse (sum) budget q1 q2 q3, by(district year type)

append using `clones'

sort district type year

list, noobs clean

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 15.1 or a fully updated version 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.