computing weighted mean over a subgroup

[abucamara]

I used the following code and data-set to compute a weighted mean over a subgroup.


input float(ppp_wshare rgdp) int year float crs

.	.	1991	2
.	.	1992	2
.	.	1993	2
.	.	1994	2
.	.	1995	2
.	.	1996	2
.	.	1997	2
.	.	1998	2
.	.	1999	2
.	.	2000	2
.	.	2001	2
0.03	.	2002	2
0.04	8.69	2003	2
0.03	0.67	2004	2
0.04	11.83	2005	2
0.04	5.36	2006	2
0.04	13.34	2007	2
0.04	3.86	2008	2
0.05	20.59	2009	2
0.05	8.44	2010	2
0.05	6.48	2011	2
0.06	13.97	2012	2
0.06	5.68	2013	2
0.06	2.7	2014	2
0.06	1.31	2015	2
0.06	2.37	2016	2
0.06	2.51	2017	2
0.05	2.5	2018	2
0.05	2.98	2019	2
0.05	3.48	2020	2
0.05	3.98	2021	2
0.05	4.48	2022	2
0.06	4.98	2023	2
0.04	2.68	1980	2
0.04	5.7	1981	2
0.05	2.9	1982	2
0.04	1.1	1983	2
0.04	2	1984	2
0.04	-1.5	1985	2

table crs year [pweight=ppp_wshare], c(mean rgdp) row col

I got the following output generated from stata using the above code which is not correct .

crs	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023
CRS_ALA	10.31	7.64	8.52	5.97	5.63	6.48	6.38	6.64	9.40	4.51	4.74	5.52	6.11	3.35	0.45	2.14	3.21	3.17	3.59	3.60	3.50	3.77
CRS_A	5.19	5.60	6.02	6.03	6.30	6.72	6.46	3.47	6.66	6.38	5.97	5.66	5.42	4.91	4.89	5.37	5.29	5.38	5.44	5.48	5.51	5.54
CRS_ME	3.87	9.67	8.88	6.47	6.36	5.43	4.18	0.26	5.27	5.30	4.97	3.65	3.20	3.13	4.44	3.33	3.60	3.78	3.62	3.59	3.66	3.61

The below output from excel is the right estimate

economy	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023
CRS ALA	10.18	7.63	8.59	6.08	5.62	6.44	6.40	6.65	9.37	4.47	4.70	5.55	6.10	3.39	0.47	2.15	3.22	3.18	3.55	3.57	3.51	3.80
CRS A	5.20	5.59	6.01	6.01	6.29	6.71	6.46	3.49	6.66	6.37	5.97	5.65	5.41	4.91	4.91	5.38	5.29	5.38	5.44	5.48	5.50	5.54
CRS ME	3.87	9.70	8.90	6.47	6.35	5.42	4.18	0.24	5.27	5.32	4.93	3.65	3.19	3.14	4.45	3.34	3.60	3.78	3.62	3.60	3.65	3.60

Any help why the difference? Why is the stata giving the wrong estimate?

[Nick Cox]

I can't see anything we can check here. You need a self-contained example with

1. Data

2. Method

3. Results.

What is your Excel code? How do you know it's right?

[abucamara]

. I have attached the data as excel.
. I used the excel formula SUMPRODUCT(A2:A3,B2:B3)/SUM(B2:B3) which was compared to that of friend and both match.

[Nick Cox]

Thanks for the extra detail. Sorry, but I am among those who won't open spreadsheet files (explanation at FAQ Advice #12) but someone else may wish to go further,