Computing ratio by subcategories of variables

[David Bicchetti]

I have the following dataset

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 ref_area str33 referencearea str5 counterpart_area str49 counterpartarea str15 measure str82 Measure_Description str14 unit_measure int time_period double obs_value
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "TAX_PAID"        "Income tax paid (on cash basis)"                                  "USD"      2021    42927479
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "TOT_REV"         "Total revenues"                                                   "USD"      2021   148803203
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "RPR"             "Related party revenues"                                           "USD"      2021     4895573
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ACT_INS"         "Insurance business activity"                                      "EST"      2021           .
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "EARNINGS"        "Accumulated earnings"                                             "USD"      2021  -221400772
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ACT_SALES"       "Sales, marketing or distribution business activity"               "EST"      2021           2
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ACT_SER"         "Provision of services to unrelated parties business activity"     "EST"      2021           2
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "PROFIT"          "Profit (loss) before income tax"                                  "USD"      2021   -46006802
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ACT_FIN"         "Regulated financial services business activity"                   "EST"      2021           1
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ENTITIES_COUNT"  "Entities"                                                         "EST"      2021          18
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ACT_PUR"         "Purchasing or procurement business activity"                      "EST"      2021           4
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "CBCR_COUNT"      "Multinational enterprise groups"                                  "ENT_MULT" 2021           5
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "STATED_CAPITAL"  "Stated capital"                                                   "USD"      2021    48663869
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ACT_OTHER"       "Other business activity"                                          "EST"      2021           2
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ACT_MAN"         "Manufacturing or production business activity"                    "EST"      2021           4
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "PROFIT_ADJ"      "Adjusted profit (loss) before income tax"                         "USD"      2021           .
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ACT_DOR"         "Dormant business activity"                                        "EST"      2021           1
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ASSETS"          "Tangible assets other than cash and cash equivalents"             "USD"      2021    78832974
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ACT_IP"          "Holding or managing intellectual property business activity"      "EST"      2021           .
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "SUBGROUPS_COUNT" "Multinational enterprise sub-groups"                              "ENT_MULT" 2021           5
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "UPR"             "Unrelated party revenues"                                         "USD"      2021   143907630
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ACT_IGF"         "Internal group finance business activity"                         "EST"      2021           .
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ACT_HOLDING"     "Holding shares or other equity instruments business activity"     "EST"      2021           .
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ACT_ADMIN"       "Administrative, management or support services business activity" "EST"      2021           2
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "ACT_RD"          "Research and development business activity"                       "EST"      2021           .
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "TAX_ACCRUED"     "Income tax accrued - current year"                                "USD"      2021           0
"ARE" "United Arab Emirates" "AFG" "Afghanistan"          "EMPLOYEES"       "Employees"                                                        "PS"       2021        3564
"ARE" "United Arab Emirates" "AGO" "Angola"               "ACT_SALES"       "Sales, marketing or distribution business activity"               "EST"      2021           .
"ARE" "United Arab Emirates" "AGO" "Angola"               "ACT_IGF"         "Internal group finance business activity"                         "EST"      2021           .
"ARE" "United Arab Emirates" "AGO" "Angola"               "ACT_DOR"         "Dormant business activity"                                        "EST"      2021           3
"ARE" "United Arab Emirates" "AGO" "Angola"               "UPR"             "Unrelated party revenues"                                         "USD"      2021   171764219
"ARE" "United Arab Emirates" "AGO" "Angola"               "ACT_OTHER"       "Other business activity"                                          "EST"      2021           .
"ARE" "United Arab Emirates" "AGO" "Angola"               "PROFIT_ADJ"      "Adjusted profit (loss) before income tax"                         "USD"      2021           .
"ARE" "United Arab Emirates" "AGO" "Angola"               "ACT_IP"          "Holding or managing intellectual property business activity"      "EST"      2021           .
"ARE" "United Arab Emirates" "AGO" "Angola"               "PROFIT"          "Profit (loss) before income tax"                                  "USD"      2021    17925795
"ARE" "United Arab Emirates" "AGO" "Angola"               "STATED_CAPITAL"  "Stated capital"                                                   "USD"      2021    10236316
"ARE" "United Arab Emirates" "AGO" "Angola"               "TAX_PAID"        "Income tax paid (on cash basis)"                                  "USD"      2021    13390865
"ARE" "United Arab Emirates" "AGO" "Angola"               "ACT_PUR"         "Purchasing or procurement business activity"                      "EST"      2021           .
"ARE" "United Arab Emirates" "AGO" "Angola"               "ACT_MAN"         "Manufacturing or production business activity"                    "EST"      2021           .
"ARE" "United Arab Emirates" "AGO" "Angola"               "SUBGROUPS_COUNT" "Multinational enterprise sub-groups"                              "ENT_MULT" 2021           4
"ARE" "United Arab Emirates" "AGO" "Angola"               "TAX_ACCRUED"     "Income tax accrued - current year"                                "USD"      2021     9822146
"ARE" "United Arab Emirates" "AGO" "Angola"               "TOT_REV"         "Total revenues"                                                   "USD"      2021   171764219
"ARE" "United Arab Emirates" "AGO" "Angola"               "ENTITIES_COUNT"  "Entities"                                                         "EST"      2021          10
"ARE" "United Arab Emirates" "AGO" "Angola"               "CBCR_COUNT"      "Multinational enterprise groups"                                  "ENT_MULT" 2021           4
"ARE" "United Arab Emirates" "AGO" "Angola"               "ACT_ADMIN"       "Administrative, management or support services business activity" "EST"      2021           .
"ARE" "United Arab Emirates" "AGO" "Angola"               "ASSETS"          "Tangible assets other than cash and cash equivalents"             "USD"      2021    22250946
"ARE" "United Arab Emirates" "AGO" "Angola"               "RPR"             "Related party revenues"                                           "USD"      2021           0
"ARE" "United Arab Emirates" "AGO" "Angola"               "EARNINGS"        "Accumulated earnings"                                             "USD"      2021    97444332
"ARE" "United Arab Emirates" "AGO" "Angola"               "ACT_SER"         "Provision of services to unrelated parties business activity"     "EST"      2021           7
"ARE" "United Arab Emirates" "AGO" "Angola"               "ACT_RD"          "Research and development business activity"                       "EST"      2021           .
"ARE" "United Arab Emirates" "AGO" "Angola"               "ACT_FIN"         "Regulated financial services business activity"                   "EST"      2021           .
"ARE" "United Arab Emirates" "AGO" "Angola"               "ACT_HOLDING"     "Holding shares or other equity instruments business activity"     "EST"      2021           .
"ARE" "United Arab Emirates" "AGO" "Angola"               "EMPLOYEES"       "Employees"                                                        "PS"       2021        3249
"ARE" "United Arab Emirates" "AGO" "Angola"               "ACT_INS"         "Insurance business activity"                                      "EST"      2021           .
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "CBCR_COUNT"      "Multinational enterprise groups"                                  "ENT_MULT" 2021          59
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ACT_HOLDING"     "Holding shares or other equity instruments business activity"     "EST"      2021         874
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "UPR"             "Unrelated party revenues"                                         "USD"      2021   2.010e+11
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "TAX_ACCRUED"     "Income tax accrued - current year"                                "USD"      2021    61896681
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ENTITIES_COUNT"  "Entities"                                                         "EST"      2021        4462
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ACT_PUR"         "Purchasing or procurement business activity"                      "EST"      2021         216
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "STATED_CAPITAL"  "Stated capital"                                                   "USD"      2021   4.430e+11
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "PROFIT"          "Profit (loss) before income tax"                                  "USD"      2021 31538024664
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "TOT_REV"         "Total revenues"                                                   "USD"      2021   2.570e+11
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ACT_SER"         "Provision of services to unrelated parties business activity"     "EST"      2021         529
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "RPR"             "Related party revenues"                                           "USD"      2021 56775290388
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ACT_FIN"         "Regulated financial services business activity"                   "EST"      2021          99
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "EARNINGS"        "Accumulated earnings"                                             "USD"      2021   1.430e+11
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ACT_MAN"         "Manufacturing or production business activity"                    "EST"      2021         135
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "TAX_PAID"        "Income tax paid (on cash basis)"                                  "USD"      2021   136782996
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ACT_IP"          "Holding or managing intellectual property business activity"      "EST"      2021          96
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ACT_ADMIN"       "Administrative, management or support services business activity" "EST"      2021         281
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ACT_INS"         "Insurance business activity"                                      "EST"      2021          24
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "EMPLOYEES"       "Employees"                                                        "PS"       2021      581795
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ACT_IGF"         "Internal group finance business activity"                         "EST"      2021          69
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "PROFIT_ADJ"      "Adjusted profit (loss) before income tax"                         "USD"      2021           .
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "SUBGROUPS_COUNT" "Multinational enterprise sub-groups"                              "ENT_MULT" 2021          59
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ACT_OTHER"       "Other business activity"                                          "EST"      2021        1004
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ASSETS"          "Tangible assets other than cash and cash equivalents"             "USD"      2021   3.420e+11
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ACT_DOR"         "Dormant business activity"                                        "EST"      2021         562
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ACT_RD"          "Research and development business activity"                       "EST"      2021          48
"ARE" "United Arab Emirates" "ARE" "United Arab Emirates" "ACT_SALES"       "Sales, marketing or distribution business activity"               "EST"      2021         525
"ARE" "United Arab Emirates" "ARG" "Argentina"            "ACT_DOR"         "Dormant business activity"                                        "EST"      2021           1
"ARE" "United Arab Emirates" "ARG" "Argentina"            "ACT_PUR"         "Purchasing or procurement business activity"                      "EST"      2021           .
"ARE" "United Arab Emirates" "ARG" "Argentina"            "ACT_IGF"         "Internal group finance business activity"                         "EST"      2021           .
"ARE" "United Arab Emirates" "ARG" "Argentina"            "PROFIT_ADJ"      "Adjusted profit (loss) before income tax"                         "USD"      2021           .
"ARE" "United Arab Emirates" "ARG" "Argentina"            "ACT_OTHER"       "Other business activity"                                          "EST"      2021           2
"ARE" "United Arab Emirates" "ARG" "Argentina"            "TOT_REV"         "Total revenues"                                                   "USD"      2021   297786434
"ARE" "United Arab Emirates" "ARG" "Argentina"            "ENTITIES_COUNT"  "Entities"                                                         "EST"      2021          16
"ARE" "United Arab Emirates" "ARG" "Argentina"            "ACT_HOLDING"     "Holding shares or other equity instruments business activity"     "EST"      2021           .
"ARE" "United Arab Emirates" "ARG" "Argentina"            "PROFIT"          "Profit (loss) before income tax"                                  "USD"      2021    35504518
"ARE" "United Arab Emirates" "ARG" "Argentina"            "TAX_PAID"        "Income tax paid (on cash basis)"                                  "USD"      2021     7470407
"ARE" "United Arab Emirates" "ARG" "Argentina"            "ACT_IP"          "Holding or managing intellectual property business activity"      "EST"      2021           .
"ARE" "United Arab Emirates" "ARG" "Argentina"            "ACT_SALES"       "Sales, marketing or distribution business activity"               "EST"      2021           2
"ARE" "United Arab Emirates" "ARG" "Argentina"            "STATED_CAPITAL"  "Stated capital"                                                   "USD"      2021    92323233
"ARE" "United Arab Emirates" "ARG" "Argentina"            "ACT_INS"         "Insurance business activity"                                      "EST"      2021           .
"ARE" "United Arab Emirates" "ARG" "Argentina"            "ACT_FIN"         "Regulated financial services business activity"                   "EST"      2021           .
"ARE" "United Arab Emirates" "ARG" "Argentina"            "EMPLOYEES"       "Employees"                                                        "PS"       2021        2007
"ARE" "United Arab Emirates" "ARG" "Argentina"            "SUBGROUPS_COUNT" "Multinational enterprise sub-groups"                              "ENT_MULT" 2021           6
"ARE" "United Arab Emirates" "ARG" "Argentina"            "TAX_ACCRUED"     "Income tax accrued - current year"                                "USD"      2021    13543107
"ARE" "United Arab Emirates" "ARG" "Argentina"            "ASSETS"          "Tangible assets other than cash and cash equivalents"             "USD"      2021   150688454
end

I would like to compute various ratio by counterpartarea, by type of measures and by year.

For instance "Employees"/ "Entities". Any suggestions, please? thank you

[Clyde Schechter]

This data organization is not very conductive to doing calculations in Stata. I think the first step is to reorganize it so that each of the measures is a separate variable:

rename obs_value _
drop Measure_Description unit_measure
reshape wide _, i(ref_area counterpart_area time_period) j(measure) string
rename _*, lower
rename _* *

Then, you can calculate your ratio as follows:

by counterpartarea time_period, sort: egen tot_employees = total(employees)
by counterpartarea time_period: egen tot_entities = total(entities_count)
gen employees_entities_ratio = tot_employees/tot_entities

In your example data, there are only four counterpart areas and one year. Each counterpart area occurs with only a single ref area. I assume this is not the case in your full data set, and I also assume that when you say "I would like to compute various ratio by counterpartarea, by type of measures and by year" you mean that for every combination nof counterpart area and year, there will be, at least potentially, multiple ref areas, and you want to total up the measures across those ref areas and then calculate the ratio of the totals. If that is not what you mean, please post back with a clearer explanation, and with a brief example that has a hand-worked result.

[David Bicchetti]

Thank you very much, this is exactly what I need. I was trying the wide transformation but for some reasons, it did not work. Apparently by removing a few variables, it works. perfect! Many thanks