How to subtract variables conditional on the group they are in

[Hans de Boer]

Dear STATA family,

I want to gather an estimate of net import flows (export-import). For that I have the dataset which you can find below. This is ordered based on country, sector and date. I want to have an estimate for (export-import) for the same country and sector in the same month. Does anyone know a quick way of doing this?

Thanks in advance!

The dataset can be found below:

country sector flow month trade
Austria Fabricated metal products, except machinery and equipment IMPORT apr2003 44740571
Austria Fabricated metal products, except machinery and equipment EXPORT apr2003 97170613
Austria Machinery and equipment n.e.c. IMPORT apr2003 1.126e+08
Austria Machinery and equipment n.e.c. EXPORT apr2003 3.131e+08
Belgium (incl. Luxembourg 'LU' -> 1998) Fabricated metal products, except machinery and equipment IMPORT apr2003 74782459
Belgium (incl. Luxembourg 'LU' -> 1998) Fabricated metal products, except machinery and equipment EXPORT apr2003 82929248
Belgium (incl. Luxembourg 'LU' -> 1998) Machinery and equipment n.e.c. EXPORT apr2003 3.533e+08
Belgium (incl. Luxembourg 'LU' -> 1998) Machinery and equipment n.e.c. IMPORT apr2003 3.686e+08
Bulgaria Fabricated metal products, except machinery and equipment IMPORT apr2003 3132479
Bulgaria Fabricated metal products, except machinery and equipment EXPORT apr2003 3875985
Bulgaria Machinery and equipment n.e.c. EXPORT apr2003 10675694
Bulgaria Machinery and equipment n.e.c. IMPORT apr2003 15280077


Regards, Hans de Boer.

[Clyde Schechter]

While the listing of information you show as example data gives the gist to human eyes, it is not readily convertible to a Stata data set for testing code, and it leaves to the imagination important metadata that will affect the best way to proceed. So the following code is untested, and may contain errors, and it relies on guesses I have made about the nature of the actual data:

gen int n_flow = -1 if flow == "IMPORT"
replace n_flow = 1 if flow == "EXPORT"

by country sector month, sort: egen double wanted = total(n_flow*trade)

Hopefully it will not be difficult to adapt this to your actual data; and it might even work as is.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Hans de Boer]

This code works wonders just as it is. Thank you mr. Schechter!

Regards, Hans de Boer.

[Hans de Boer]

Dear mr. Schechter or anyone else looking at this post,

I have almost the same question, but, instead of subtracting imports from exports, now I want to create an import-export ratio (dividing imports by exports) for the same sector and country in each month.

Is there a way to alter the code you kindly provided me, so that I can create this ratio?

Kind regards, Hans de Boer.

[Hans de Boer]

input str29 country    str57 sector str6 flow long month double trade
"Portugal"    "Food products"    "EXPORT"    77    25435248
"Czechia"    "Chemicals (including pharma)"    "IMPORT"    77    86537173
"Poland"    "Iron and steel"    "IMPORT"    77    16803712
"Germany"    "Aluminium"    "EXPORT"    77    188619157
"Croatia"    "Other non-metallic mineral products"    "EXPORT"    77    3597607
"Croatia"    "Paper and paper products"    "EXPORT"    77    1339071
"Croatia"    "Textiles"    "EXPORT"    77    954741
"Romania"    "Chemicals (including pharma)"    "EXPORT"    77    28259217
"Czechia"    "Cement"    "IMPORT"    77    14377
"Ireland"    "Other non-metallic mineral products"    "EXPORT"    77    22463997
"Czechia"    "Coke and refined petroleum products"    "IMPORT"    77    3294956
"Bulgaria"    "Textiles"    "IMPORT"    77    15967653
"Estonia"    "Basic metals"    "EXPORT"    77    1968863
"Spain"    "Basic metals"    "EXPORT"    77    135588910
"Croatia"    "Other porcelain and ceramic products"    "EXPORT"    77    182278
"Italy"    "Textiles"    "EXPORT"    77    359123540
"Malta"    "Coke and refined petroleum products"    "IMPORT"    77    2757552

[Clyde Schechter]

Well, the example data you show is not suitable for this calculation because there are no country-sector-month combinations in it that actually have both an import and an export observation.

Assuming that your full data set does not suffer this limitation:

assert inlist(flow, "EXPORT", "IMPORT")
isid country sector month flow, sort
by country sector month (flow): assert _N <= 2
by country sector month (flow): gen imp_exp_ratio = trade[2]/trade[1]

[Hans de Boer]

The code works. Thank you mr. Schechter for your great help once again!