Calculate the difference of the value of one observation with the average value for observations in other groups

[Meng JI]

Hi everyone,

I have a question of how to use stata to calculate the difference of the value of one observation with the average value for observations in other groups

My data structure looks like this:

Obs	Group	Order Value	Difference
1	A	200	-60
2	A	120	-140
3	A	254	-6
4	B	135	-111.5
5	C	240	62.75
6	C	265	87.75
7	C	400	222.75

I want to calculate the order value difference between one observation and the mean of the order value for all the observations in other groups.

For instance, for observation 1, I want to calculate the average order value for observations that are in other groups, and then calculate the difference between the order value for observation 1 and the average order value in other groups. (135+240+265+400)/4=260, then I get 200-260=-60; for observation 2, I get 120-260=-140 etc.

I have a huge dataset and can't do it manually. I think I need to write some loops to do that but haven't been able to do that since I'm pretty new to Stata.

I wonder if you know any resources that I can refer to.

Thank you so much!

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input byte obs str2 group int ordervalue
1 "A " 200
2 "A " 120
3 "A " 254
4 "B " 135
5 "C " 240
6 "C " 265
7 "C " 400
end

summ ordervalue, meanonly
local N `r(N)'
local grand_total `r(sum)'

by group, sort: egen group_total = total(ordervalue)
by group: gen others_mean = (`grand_total'-group_total)/(`N'-_N)
gen wanted = ordervalue - others_mean

I have a huge dataset and can't do it manually.

Even with a tiny data set, you shouldn't even think about doing any calculations manually. Unless you are just playing around, you need an audit trail of all the calculations you do. Without that, there's no reason anybody should take your results seriously.

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 17, 16 or a fully updated version 15.1 or 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.

Finally, thank you for the clear explanation of your question.

[Meng JI]

* Example generated by -dataex-. For more info, type help dataex
clear
input byte obs str2 group int ordervalue
1 "A " 200
2 "A " 120
3 "A " 254
4 "B " 135
5 "C " 240
6 "C " 265
7 "C " 400
end

summ ordervalue, meanonly
local N `r(N)'
local grand_total `r(sum)'

by group, sort: egen group_total = total(ordervalue)
by group: gen others_mean = (`grand_total'-group_total)/(`N'-_N)
gen wanted = ordervalue - others_mean

Even with a tiny data set, you shouldn't even think about doing any calculations manually. Unless you are just playing around, you need an audit trail of all the calculations you do. Without that, there's no reason anybody should take your results seriously.

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 17, 16 or a fully updated version 15.1 or 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.

Finally, thank you for the clear explanation of your question.

Hi Clyde,

Thank you so much for your help. The code works very well and solves my problem.

In addition to that, thank you for your kind suggestions with respect to how to think about the data analyzing process and detailed illustration of how to post examples in the forum. Really appreciate your help.

Great thanks!