How do I subtract within a column based on criteria from other columns?

[John Vitor]

I am looking at immigration data, looks somewhat like this:

Austria	Belgium	1999	Inflow	2,100
Austria	Belgium	1999	Outflow	1,500
Germany	Portugal	2000	Inflow	1000
Germany	Portugal	2000	Outflow	2000

As you can see, rows 1 and 2 are for the same country and same year, the only difference being "type of migration"; in one case it is inflow, in the next, outflow (the same is true for 3 and 4)

How would I go about creating a "net inflow" variable, which would simply be the inflow - outflow for each country, for each year?

[Rich Goldstein]

you don't tell us what your variable names are (or provide the data in a way that is easy to import - see the FAQ on -dataex-) but try this using your actual variable names:

sort c1 c2 year type
gen netflow=flow-flow[_n+1] if c1==c1[_n+1[ & c2==c2[_n+1] & year==year[_n+1]

assuming that your only "types" are Inflow and Outflow, this will give you in minus out

note that I have assumed you wanted to track by both c1 and c2; if you want totals for c1 regardless of c2, that will be a little different

[John Vitor]

you don't tell us what your variable names are (or provide the data in a way that is easy to import - see the FAQ on -dataex-) but try this using your actual variable names:

Sorry about that, when formatting the table I originally put the variable names in
Country__| BirthCountry| Year| Type | Migration

Austria	Belgium	1999	Inflow	2,100
Austria	Belgium	1999	Outflow	1,500
Germany	Portugal	2000	Inflow	1000
Germany	Portugal	2000	Outflow	2000

I think this should work now:

EDIT: I ran the command you suggested and it worked beautifully. Thank you so much

[Nick Cox]

Also posted at https://www.reddit.com/r/stata/comments/66jezq/how_to_subtract_values_within_a_column_based_on/

Please note our cross-posting policy, which is that you should tell us about it. http://www.statalist.org/forums/help#crossposting

If data are all as in your example then

bysort Country Birth Year (Type) : gen Difference = Migration[2] - Migration[1]

should get you there. The main ideas are discussed at more length at http://www.stata-journal.com/sjpdf.h...iclenum=dm0043 and searching this form for dm0043 is sufficiently specific to find related threads.