bysort egen with difference

[tania kallab]

Hi all,

I hve this data set

ctry_year_a	ctry_year_b	trade	countrya_Exp	countryb_imp
21882	401882	3.80E+08	6.31E+09	4.58E+08
21883	401883	4.07E+08	7.16E+09	4.88E+08
21884	401884	3.52E+08	6.58E+09	4.28E+08
21885	401885	2.78E+08	6.51E+09	3.87E+08
21886	401886	3.42E+08	6.93E+09	4.55E+08
21887	401887	3.19E+08	7.08E+09	4.21E+08
21888	401888	3.11E+08	6.93E+09	4.14E+08
21889	401889	3.35E+08	7.67E+09	4.49E+08
21890	401890	3.64E+08	8.64E+09	4.93E+08
21891	401891	4.01E+08	8.92E+09	5.44E+08
21892	401892	5.45E+08	1.01E+10	7.27E+08
21893	401893	5.89E+08	9.12E+09	7.31E+08
21894	401894	6.12E+08	9.33E+09	7.68E+08
21895	401895	4.10E+08	9.40E+09	5.85E+08

I would like to generate a new variable called TB under the following condition: when cntry_yr_a = cntry_yr_b (they are not horozontally equal, stata will have to search for the correspondign row), i want the difference between countrya_exp and countryb_imp
basically the trade balance

I tried bysort country_year_a cuontry_year_b: egen TB=countrya_exp - countryb_imp,
i am getting an error
can someone help me on that

[Nick Cox]

egen is quite illegal here as every egen call must make use of an egen function. generate rather than egen would be legal but is not what you want.

The condition isn't true for any pair of observations in your example. Please do read and act on FAQ Advice #12 to give better data examples.

See http://www.stata-journal.com/sjpdf.h...iclenum=dm0043 for some technique and search the forum for mentions of dm0043 for examples.

[Carlo Lazzaro]

Tania:
welcome to the list.
Waiting for you to act on FAQ recommendations and Nick's advice, maybe what follows can be helpful:

. input ctry_year_a ctry_year_b trade countrya_Exp countryb_imp

     ctry_ye~a  ctry_ye~b      trade  countr~xp  countr~mp
  1.
. 21882 401882 3.80E+08 6.31E+09 4.58E+08
  2.
. 21883 401883 4.07E+08 7.16E+09 4.88E+08
  3.
. 21884 401884 3.52E+08 6.58E+09 4.28E+08
  4.
. 21885 401885 2.78E+08 6.51E+09 3.87E+08
  5.
. 21886 401886 3.42E+08 6.93E+09 4.55E+08
  6.
. 21887 401887 3.19E+08 7.08E+09 4.21E+08
  7.
. 21888 401888 3.11E+08 6.93E+09 4.14E+08
  8.
. 21889 401889 3.35E+08 7.67E+09 4.49E+08
  9.
. 21890 401890 3.64E+08 8.64E+09 4.93E+08
 10.
. 21891 401891 4.01E+08 8.92E+09 5.44E+08
 11.
. 21892 401892 5.45E+08 1.01E+10 7.27E+08
 12.
. 21893 401893 5.89E+08 9.12E+09 7.31E+08
 13.
. 21894 401894 6.12E+08 9.33E+09 7.68E+08
 14.
. 21895 401895 4.10E+08 9.40E+09 5.85E+08
 15.
. end

. replace ctry_year_a= ctry_year_a-21000
(14 real changes made)

. replace ctry_year_b = ctry_year_b-401000
(14 real changes made)

. egen Group=group( ctry_year_a ctry_year_b )

. bysort Group: g diff= countrya_Exp- countryb_imp

[tania kallab]

Thank you both for your replies
so i am showing you an expanded version of the data, hoping it might make my problem clearer

cntryida	cntryidb	dyad	countrya	countryb	yeara	yearb	ctry_year_a	ctry_year_b	trade	countrya_Exp	countryb_imp
2	40	240	usa	cub	1882	1882	21882	401882	3.80E+08	6.31E+09	4.58E+08
2	40	240	usa	cub	1883	1883	21883	401883	4.07E+08	7.16E+09	4.88E+08
2	40	240	usa	cub	1884	1884	21884	401884	3.52E+08	6.58E+09	4.28E+08
2	40	240	usa	cub	1885	1885	21885	401885	2.78E+08	6.51E+09	3.87E+08
2	40	240	usa	cub	1886	1886	21886	401886	3.42E+08	6.93E+09	4.55E+08
2	40	240	usa	cub	1887	1887	21887	401887	3.19E+08	7.08E+09	4.21E+08
2	40	240	usa	cub	1888	1888	21888	401888	3.11E+08	6.93E+09	4.14E+08
2	40	240	usa	cub	1889	1889	21889	401889	3.35E+08	7.67E+09	4.49E+08
2	40	240	usa	cub	1890	1890	21890	401890	3.64E+08	8.64E+09	4.93E+08
2	40	240	usa	cub	1891	1891	21891	401891	4.01E+08	8.92E+09	5.44E+08
2	40	240	usa	cub	1892	1892	21892	401892	5.45E+08	1.01E+10	7.27E+08
2	40	240	usa	cub	1893	1893	21893	401893	5.89E+08	9.12E+09	7.31E+08

the value trade is the export from countrya to countryb in a particular year

the closest i could get is:
egen TB=total( trade), by ( ctry_year_a ctry_year_b)
which is basically what i want with a slight difference instead of total(trade) i want sth like subtract (trade)

i hope i made my question clearer now

thx alot

[Nick Cox]

What you want is clear to me and I've already pointed you to technique for dyads.

Similarly I flag again, this time with a clickable link, the advice at http://www.statalist.org/forums/help#stata -- which is to use dataex (SSC) and CODE delimiters.

[tania kallab]

[CODE]
* ----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(cntryida cntryidb) long dyad str9 countrya str10 countryb int(yeara yearb) long(ctry_year_a ctry_year_b) double trade
2 200 2200 "usa" "uk"  1870 1870 21870 2001870 1.640e+09
2 220 2220 "usa" "fra" 1870 1870 21870 2201870 3.680e+08
2  70  270 "usa" "mex" 1871 1871 21871  701871  31488947
2 140 2140 "usa" "bra" 1871 1871 21871 1401871 1.390e+08
2 200 2200 "usa" "uk"  1871 1871 21871 2001871 1.970e+09
2 210 2210 "usa" "net" 1871 1871 21871 2101871  51156909
2 211 2211 "usa" "bel" 1871 1871 21871 2111871  60173118
2 220 2220 "usa" "fra" 1871 1871 21871 2201871 2.900e+08
2 230 2230 "usa" "spa" 1871 1871 21871 2301871  61651097
2 235 2235 "usa" "por" 1871 1871 21871 2351871   7748837
end

HI again,

I come back for the same issue, it took some time trying to figure a way but did not manage so here again, i copying part of my data from dataex
i would like to calculate the difference countrya_exp countryb_imp sorted by cntry_year_a ctry_year_b

that is the best i could explain, i hope i will get an answer because i am really stuck here

thank you in advance

PS: this is a portion of the sample, the cntryida contains other countries than "USA"