Generating domestic trade flows

[Ridwan Sheikh]

Dear Clyde Schechter

I am working with international trade data. Each observation is an export flows from country i to country j at time t.

DATA 1

* Example generated by -dataex-. To install: ssc install dataex
clear
input str3(iso_i iso_j) int Year double(GDP_it Exports_ijt)
"USA" "FRA" 1990 10.5  1.7
"USA" "DEU" 1990 10.5  3.2
"USA" "GBR" 1990 10.5  2.5
"DEU" "PRT" 1990 20.3  9.4
"DEU" "USA" 1990 20.3  6.7
"DEU" "LTU" 1990 20.3  5.9
"USA" "FRA" 1991 13.7  8.6
"USA" "TUN" 1991 13.7  3.2
"USA" "GBR" 1991 13.7  9.5
"DEU" "GBR" 1991 22.4  7.3
"DEU" "USA" 1991 22.4  8.4
"DEU" "LTU" 1991 22.4  3.5
end

For each year. I need to create an additional obervation on domestic trade flows i = j.
The domestic trade is calculated as

X_ii = GDP_it - Σ _{j# i} X_ijt

that is domestic trade X_ii (i = j) is the difference between GDP of exporter and sum of all its exports to the world (Σ _{j# i} X_ijt).

In this example, USA in 1990 is exporting to FRA, DEU, and GBR. I aggregate the export flows of USA across all partners to obtain the total exports of USA to the rest of world in year 1990 (i.e., Σ _{j# i} X_ijt = 1.7 + 3.2 + 2.5 = 7.4). The GDP_USA,1990 = 10.5 , therefore dometic trade flows are obtained as:

Exports_USA-USA,1990 = GDP_USA,1990 - Σ _{j# i} X_ijt = 10.5 - 7.4 = 3.1

Following same procedure for other countries, will give us a data like

DATA 2

* Example generated by -dataex-. To install: ssc install dataex
clear
input str3(iso_i iso_j) int Year double(GDP_it Exports_ijt)
"USA" "FRA" 1990 10.5  1.7
"USA" "DEU" 1990 10.5  3.2
"USA" "GBR" 1990 10.5  2.5
"USA" "USA" 1990 10.5  3.1
"DEU" "PRT" 1990 20.3  9.4
"DEU" "USA" 1990 20.3  6.7
"DEU" "LTU" 1990 20.3  5.9
"DEU" "DEU" 1990 20.3 -1.7
"USA" "FRA" 1991 13.7  8.6
"USA" "TUN" 1991 13.7  3.2
"USA" "GBR" 1991 13.7  9.5
"USA" "USA" 1991 13.7  7.6
"DEU" "GBR" 1991 22.4  7.3
"DEU" "USA" 1991 22.4  8.4
"DEU" "LTU" 1991 22.4  3.5
"DEU" "DEU" 1991 22.4  3.2
end

How do I get from DATA 1 to DATA 2.

Thanks and regards,
(Ridwan)

[Clyde Schechter]

by iso_i Year, sort: egen all_exports_it = total(Exports_ijt)
gen domestic_trade = GDP_it - all_exports_it

It is not usually a good idea to address your post to me, or any other individual, unless it is a continuation of a dialog that has already been established in the same thread. There are many Forum members who could have responded to you in the hours before I saw it.

By the way, your "DATA2" is just a copy of your "DATA1."

[Ridwan Sheikh]

Thanks Clyde Schechter . I am sorry, I will be careful in future.

The domestic_trade in the code you sent, creates domestic trade flows in a seperate column. I want them in a row.

iso_i	iso_j	year	GDP_it	Exports_ijt
USA	FRA	1990	10.5	1.7
USA	DEU	1990	10.5	3.2
USA	GBR	1990	10.5	2.5
USA	USA	1990	10.5	3.5

Where the last row (in red) denotes the domestic trade flows with iso_i = iso_j . Similarily for other countries in the panel.

Therefore, DATA 2 includes information on both international (observations with iso_i # iso_j) and domestic (iso_i = iso_j) trade flows for each country. It is a copy of DATA 1 but additionally includes information on domestic trade flows.

How to obtain this data structure ? was my question.

Thanks and regards,
(Ridwan)

[George Ford]

probably collapse, save, and then append.

[Ridwan Sheikh]

Thanks George Ford , this seems an easy way to go

use master, clear

by iso_i Year, sort: egen all_exports_it = total(Exports_ijt)
gen domestic_trade = GDP_it - all_exports_it

collapse domestic_trade GDP_it, by(iso_i Year)

gen iso_j=iso_i

rename domestic_trade Exports_ijt

order iso_j Year GDP_it Exports_ijt, a(iso_i)

save domestic

use master, clear
append using domestic

I guess there must be an efficient (short) way to do the same.

Thanks,
(Ridwan)

[George Ford]

use master, clear
preserve
    by iso_i Year, sort: egen all_exports_it = total(Exports_ijt)
    gen domestic_trade = GDP_it - all_exports_it
    collapse domestic_trade GDP_it, by(iso_i Year)
    gen iso_j=iso_i
    rename domestic_trade Exports_ijt
    order iso_j Year GDP_it Exports_ijt, a(iso_i)
    save domestic , replace
restore
append using domestic

[Ridwan Sheikh]

Thanks George Ford ! that is even better way to do.