Aggregating 4 digit sectoral data into 2 digit

[Hugo Rocha]

Dear all,

I have trade data in 4-digit level data (with the goods they represent). In order to answer my research question, I need to merge these data with a sectoral 2-digit level data. My goal is to aggregate the 4-digit level data into 2-digit level data. For instance, if I have data that is 1511 (Beef processing) and 1512 (Fish processing), I would like to add the corresponding variables (exports, imports, etc) with the mentioned codes and label the new variable as 15 (Food Processing). Is there a quick way of doing this?

Please find below the dataex for the 4 digit level data.

 input float country_industry long(ISIC1 ISICCombination1 Year1) float(ImportsWorld_numeric ExportsWorld_numeric)
1 1  1 13      .      .
1 1  1 14      .      .
1 1  1 15      .      .
1 1  1 16      .      .
1 1  1 17      .      .
1 1  1 18      .      .
1 1  1 19      7   3685
1 1  1 20      .    133
1 1  1 21      .   1464
1 1  1 22      3    401
1 1  1 23      .   1015
1 1  1 24      .    191
1 1  1 25      .    271
1 1  1 26      .   2074
1 1  1 27      .   3726
1 1  1 29   6341   4285
2 2  4 13      .      .
2 2  4 14      .      .
2 2  4 15      .      .
2 2  4 16      .      .
2 2  4 17      .      .
2 2  4 18      .      .
2 2  4 19      .      .
2 2  4 20      .      .
2 2  4 21      .      .
2 2  4 22      .      .
2 2  4 23      .      .
2 2  4 24      .      .
2 2  4 25      .      .
2 2  4 26      .      .
2 2  4 27      .      .
2 2  4 29   6844      .
3 3  7 13      .      .
3 3  7 14      .      .
3 3  7 15      .      .
3 3  7 16      .      .
3 3  7 17      .      .
3 3  7 18      .      .
3 3  7 19      . 173097
3 3  7 20      . 101670
3 3  7 21      .  70352
3 3  7 22      .  81306
3 3  7 23      .      .
3 3  7 24      .      .
3 3  7 25      .      .
3 3  7 26      .  99288
3 3  7 27      .  73063
3 3  7 29  21025 124345
4 4  9 13      .      .
4 4  9 14      .      .
4 4  9 15      .      .
4 4  9 16      .      .
4 4  9 17      .      .
4 4  9 18      .      .
4 4  9 19      .      .
4 4  9 20      .      .
4 4  9 21      .      .
4 4  9 22      .      .
4 4  9 23      .      .
4 4  9 24      .      .
4 4  9 25      .      .
4 4  9 26      .      .
4 4  9 27      .      .
4 4  9 29  15752      0
5 5 11 13      .      .
5 5 11 14      .      .
5 5 11 15      .      .
5 5 11 16      .      .
5 5 11 17      .      .
5 5 11 18      .      .
5 5 11 19  15760    177
5 5 11 20   7765    157
5 5 11 21  24532     36
5 5 11 22  30868    217
5 5 11 23      .      .
5 5 11 24      .      .
5 5 11 25      .      .
5 5 11 26  53557    121
5 5 11 27  46521    200
5 5 11 29  74089     57
6 6 12 13      .      .
6 6 12 14      .      .
6 6 12 15      .      .
6 6 12 16      .      .
6 6 12 17      .      .
6 6 12 18      .      .
6 6 12 19 162443      .
6 6 12 20 359929      .
6 6 12 21 229567      .
6 6 12 22 220048      .
6 6 12 23 175498      .
6 6 12 24 429087      .
6 6 12 25 498933      .
6 6 12 26 473225      .
6 6 12 27 668231      .
6 6 12 29 774737      0
7 7 16 13      .      .
7 7 16 14      .      .
7 7 16 15      .      .
7 7 16 16      .      .
end
label values ISIC1 ISIC1
label def ISIC1 1 "1511", modify
label def ISIC1 2 "1512", modify
label def ISIC1 3 "1513", modify
label def ISIC1 4 "1514", modify
label def ISIC1 5 "1520", modify
label def ISIC1 6 "1531", modify
label def ISIC1 7 "1532", modify
label values ISICCombination1 ISICCombination1
label def ISICCombination1 1 "1511", modify
label def ISICCombination1 4 "1512", modify
label def ISICCombination1 7 "1513", modify
label def ISICCombination1 9 "1514", modify
label def ISICCombination1 11 "1520", modify
label def ISICCombination1 12 "1531", modify
label def ISICCombination1 16 "1532", modify
label values Year1 Year1
label def Year1 13 "2002", modify
label def Year1 14 "2003", modify
label def Year1 15 "2004", modify
label def Year1 16 "2005", modify
label def Year1 17 "2006", modify
label def Year1 18 "2007", modify
label def Year1 19 "2008", modify
label def Year1 20 "2009", modify
label def Year1 21 "2010", modify
label def Year1 22 "2011", modify
label def Year1 23 "2012", modify
label def Year1 24 "2013", modify
label def Year1 25 "2014", modify
label def Year1 26 "2015", modify
label def Year1 27 "2016", modify
label def Year1 29 "2018", modify

Thank you very much,

Hugo

[Andrew Musau]

decode ISIC1, gen(sic2) maxlength(2)

[Hugo Rocha]

Thanks! And one more question to add the variables (for instance, exports in the sectors of fishing, meat fruits into exports for food)? Thanks!

[Andrew Musau]

After #2

collapse (sum) ImportsWorld_numeric ExportsWorld_numeric, by(country_industry Year1 sic2)

If you need the identifiers, then you need to create a variable that tells you what each sic2 classification represents beforehand.

[Hugo Rocha]

sounds good! Thank you very much!