Expand data and replacing multiple observations using elements of a local variable

[Hugo Ferrer]

Hi all,

This is my first post, so I apologize in advance if anything needs clarification.

I am working on trade flows and have some data for the ReporterCountry, PartnerCountry, T_Product (type of product traded) and year. Here is a working example of my dataset:

input str3 Rep_country str4 Part_country str2 T_product str4 year
USA CAN X1 2000
FRA AUS X2 2003
RUS EU15 X1 2001
JPN BRA X3 2000
ARG EU15 X2 2004
end

And, I need some help with substituting the observation "EU15" in the variable PartnerCountry by each one of the 15 European countries, keeping all else equal. I mean, for the route RUS-EU15-X1-2001, there would be 15 rows and for each of the 15 rows, RUS will be the reporter country, X1 the product traded, 2001 the year and in the partner_country variable will have to appear AUT in the first row, BEL in the second, and so on.

I have coded some lines but I do not know if my approach is even correct because it reports an error. So any help would be very welcome. Thanks!

egen id = group(Rep_country Part_country T_product)
sort id
local EU15 AUT BEL DEU DNK ESP FIN FRA GBR GRC IRL ITA LUX NLD PRT SWE
local N : word count `EU15'
expand `N' if Part_country == "EU15"
bys id (year) : gen tag = _n
gen newpartner = .
local EU15 AUT BEL DEU DNK ESP FIN FRA GBR GRC IRL ITA LUX NLD PRT SWE
local N : word count `EU15'
forval i = 1/`N'{
    di `i'
    local new : word `i' of `EU15'
    di "`new'"
    replace newpartner = "`new'" if tag == `i'
}

1
AUT
type mismatch
r(109);

end of do-file

r(109);

[Andrew Musau]

clear
input str3 Rep_country str4 Part_country str2 T_product str4 year
USA CAN X1 2000
FRA AUS X2 2003
RUS EU15 X1 2001
JPN BRA X3 2000
ARG EU15 X2 2004
end

expand 15 if Part_country=="EU15"
local EU15 "AUT BEL DEU DNK ESP FIN FRA GBR GRC IRL ITA LUX NLD PRT SWE"
local i 1
sort Rep_country
foreach country of local EU15{
    quietly{
        by Rep_country: replace Part_country="`country'" if _n==`i' & Part_country=="EU15"
        local ++i
    }
}

Res.:

. l, sepby(Rep_country)

     +---------------------------------------+
     | Rep_co~y   Part_c~y   T_prod~t   year |
     |---------------------------------------|
  1. |      ARG        AUT         X2   2004 |
  2. |      ARG        BEL         X2   2004 |
  3. |      ARG        DEU         X2   2004 |
  4. |      ARG        DNK         X2   2004 |
  5. |      ARG        ESP         X2   2004 |
  6. |      ARG        FIN         X2   2004 |
  7. |      ARG        FRA         X2   2004 |
  8. |      ARG        GBR         X2   2004 |
  9. |      ARG        GRC         X2   2004 |
 10. |      ARG        IRL         X2   2004 |
 11. |      ARG        ITA         X2   2004 |
 12. |      ARG        LUX         X2   2004 |
 13. |      ARG        NLD         X2   2004 |
 14. |      ARG        PRT         X2   2004 |
 15. |      ARG        SWE         X2   2004 |
     |---------------------------------------|
 16. |      FRA        AUS         X2   2003 |
     |---------------------------------------|
 17. |      JPN        BRA         X3   2000 |
     |---------------------------------------|
 18. |      RUS        AUT         X1   2001 |
 19. |      RUS        BEL         X1   2001 |
 20. |      RUS        DEU         X1   2001 |
 21. |      RUS        DNK         X1   2001 |
 22. |      RUS        ESP         X1   2001 |
 23. |      RUS        FIN         X1   2001 |
 24. |      RUS        FRA         X1   2001 |
 25. |      RUS        GBR         X1   2001 |
 26. |      RUS        GRC         X1   2001 |
 27. |      RUS        IRL         X1   2001 |
 28. |      RUS        ITA         X1   2001 |
 29. |      RUS        LUX         X1   2001 |
 30. |      RUS        NLD         X1   2001 |
 31. |      RUS        PRT         X1   2001 |
 32. |      RUS        SWE         X1   2001 |
     |---------------------------------------|
 33. |      USA        CAN         X1   2000 |
     +---------------------------------------+

.

[Hugo Ferrer]

Thanks a lot Andrew!!!