How to create a variable based on values in different rows

Question

I have a Stata dataset organized as follows:

payment   class    molecule State
10          1       1        1
8           2       1        1
25          3       2        1
7           4       2        1
12          1       1        2
5           2       1        2
24          3       2        2
7           4       2        2

How do I create a variable that is the difference of the payment variable between classes within the same molecule?

Expected output:

payment   class    molecule    State payment_difference
10          1       1            1      2
8           2       1            1      2
25          3       2            1      18
7           4       2            1      18
12          1       1            2      7
5           2       1            2      7
24          3       2            2      17
7           4       2            2      17

Answer 1

Using your toy example:

clear

input payment class molecule state
10 1 1 1
8  2 1 1
25 3 2 1
7  4 2 1
12 1 1 2
5  2 1 2
24 3 2 2
7  4 2 2
end

The following works for me:

bysort state molecule (class) : generate diff = payment[1] - payment[2]

list, separator(0)

     +-------------------------------------------+
     | payment   class   molecule   state   diff |
     |-------------------------------------------|
  1. |      10       1          1       1      2 |
  2. |       8       2          1       1      2 |
  3. |      25       3          2       1     18 |
  4. |       7       4          2       1     18 |
  5. |      12       1          1       2      7 |
  6. |       5       2          1       2      7 |
  7. |      24       3          2       2     17 |
  8. |       7       4          2       2     17 |
     +-------------------------------------------+

For details, read Speaking Stata: How to move step by: step on Stata Journal.