Creating a new observation/row (from existing observations)

[meara algama]

I want to create a new observation for each year, which is a subtraction of two existing rows. Please advice!

My simplified data is as follows;

fips inid gdp2005 gdp2006 gdp2007
1 2 3000 5000 35000
1 4 230 430 800
2 2 5000 4000 4300
2 4 . 200 150

(inid ==2 - inid==4)
The new data should look like :

fips inid gdp2005 gdp2006 gdp2007
1 2 3000 5000 3500
1 4 230 430 800
1 . 2770 4570 2700
2 2 5000 4000 4300
2 4 . 200 150
2....

[Nick Cox]

Don't do this. It's spreadsheet thinking and just gives you two kinds of observations and the obligation henceforth to ignore whichever kind is not relevant.

In fact your data structure appears unfit for almost any Stata purpose.

35000 looks like a typo in the first listing.

clear 
input fips inid gdp2005 gdp2006 gdp2007
1 2 3000 5000 3500
1 4 230 430 800
2 2 5000 4000 4300
2 4 . 200 150
end 
reshape long gdp , i(fips inid) j(year) 

bysort fips year (inid) : gen diff = gdp[1] - gdp[2] 

list , sepby(fips year) 

    +----------------------------------+
     | fips   inid   year    gdp   diff |
     |----------------------------------|
  1. |    1      2   2005   3000   2770 |
  2. |    1      4   2005    230   2770 |
     |----------------------------------|
  3. |    1      2   2006   5000   4570 |
  4. |    1      4   2006    430   4570 |
     |----------------------------------|
  5. |    1      2   2007   3500   2700 |
  6. |    1      4   2007    800   2700 |
     |----------------------------------|
  7. |    2      2   2005   5000      . |
  8. |    2      4   2005      .      . |
     |----------------------------------|
  9. |    2      2   2006   4000   3800 |
 10. |    2      4   2006    200   3800 |
     |----------------------------------|
 11. |    2      2   2007   4300   4150 |
 12. |    2      4   2007    150   4150 |
     +----------------------------------+

.

[meara algama]

Thank you! This worked like a charm. Re-shaping data seems to be a really powerful tool!