# st: calculation between observations

 From "Nick Cox" To Subject st: calculation between observations Date Mon, 8 Sep 2003 23:01:35 +0100

```Miriam Manchin

> I have a dataset in the following form:
>
> REPORTING	VALUE	TRADE	PARTNER	PRODUCT
> group	8	4	001	9
> a	.	3	001	9
> b	.	1	001	9
> group	6	8	002	7
> a	.	8	002	7
> group	5	4	003	7
> b	.	3	003	7
> c	.	1	003	7
>
> So I have observations called group for which trade is the
> sum of the
> observations belonging to the group where partner and
> product is the
> same. The variable "value" is missing for the group
> members. There are
> always different number of observations belonging to the group. I
> would like to calculate the missing values for the group
> members for the
> variable "value" using proportions given by the variable trade (for
> example when partner=001 & product=9 the value for "a" would be 6
> and "b" would be 2).
>

This strikes me as a rather strange data structure. I am
not sure whether to recommend a reshape. Nevertheless
the following seems to fit your example:

. gen block = sum(reporting == "group")

. l

+------------------------------------------------------+
| report~g   value   trade   partner   product   block |
|------------------------------------------------------|
1. |    group       8       4         1         9       1 |
2. |        a       .       3         1         9       1 |
3. |        b       .       1         1         9       1 |
4. |    group       6       8         2         7       2 |
5. |        a       .       8         2         7       2 |
|------------------------------------------------------|
6. |    group       5       4         3         7       3 |
7. |        b       .       3         3         7       3 |
8. |        c       .       1         3         7       3 |
+------------------------------------------------------+

. egen total = sum(value), by(block)

. l

+--------------------------------------------------------------+
| report~g   value   trade   partner   product   block   total |
|--------------------------------------------------------------|
1. |    group       8       4         1         9       1       8 |
2. |        a       .       3         1         9       1       8 |
3. |        b       .       1         1         9       1       8 |
4. |    group       6       8         2         7       2       6 |
5. |        a       .       8         2         7       2       6 |
|--------------------------------------------------------------|
6. |    group       5       4         3         7       3       5 |
7. |        b       .       3         3         7       3       5 |
8. |        c       .       1         3         7       3       5 |
+--------------------------------------------------------------+

. bysort block (value) : replace value = value[1] * trade/trade[1] if
mi(value)
value was byte now float

. l

+--------------------------------------------------------------+
| report~g   value   trade   partner   product   block   total |
|--------------------------------------------------------------|
1. |    group       8       4         1         9       1       8 |
2. |        a       6       3         1         9       1       8 |
3. |        b       2       1         1         9       1       8 |
4. |    group       6       8         2         7       2       6 |
5. |        a       6       8         2         7       2       6 |
|--------------------------------------------------------------|
6. |    group       5       4         3         7       3       5 |
7. |        b    3.75       3         3         7       3       5 |
8. |        c    1.25       1         3         7       3       5 |
+--------------------------------------------------------------+

Nick
n.j.cox@durham.ac.uk

*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
```