Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Converting data in matrix format (input-output table) to a Stata friendly format

    Hi everyone,

    I have input output table data (by countries and sectors for a specific year) in a matrix format that I would like to convert to a Stata friendly format. It is the final demand part of the matrix that I would like to work on (1140 columns and 4915 lines).

    The data comes in three separate .txt files, two for the labels and one for the data itself.

    This is an example from the columns labels file which are the final demand categories for each country (1140 rows):

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input str3 v1 str47 v2
"AFG" "Household final consumption P.3h"              
"AFG" "Non-profit institutions serving households P.3n"
"AFG" "Government final consumption P.3g"              
"AFG" "Gross fixed capital formation P.51"            
"AFG" "Changes in inventories P.52"                    
"AFG" "Acquisitions less disposals of valuables P.53"  
"ALB" "Household final consumption P.3h"              
"ALB" "Non-profit institutions serving households P.3n"
"ALB" "Government final consumption P.3g"              
"ALB" "Gross fixed capital formation P.51"            
"ALB" "Changes in inventories P.52"                    
"ALB" "Acquisitions less disposals of valuables P.53"  
"DZA" "Household final consumption P.3h"              
"DZA" "Non-profit institutions serving households P.3n"
"DZA" "Government final consumption P.3g"              
"DZA" "Gross fixed capital formation P.51"            
"DZA" "Changes in inventories P.52"                    
"DZA" "Acquisitions less disposals of valuables P.53"    
end
    This is an example from the rows labels which are the sectors for each country (4915 rows):

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input str3 v1 str53 v2
"AFG" "Agriculture"                                          
"AFG" "Fishing"                                              
"AFG" "Mining and Quarrying"                                
"AFG" "Food & Beverages"                                    
"AFG" "Textiles and Wearing Apparel"                        
"AFG" "Wood and Paper"                                      
"AFG" "Petroleum, Chemical and Non-Metallic Mineral Products"
"AFG" "Metal Products"                                      
"AFG" "Electrical and Machinery"                            
"AFG" "Transport Equipment"                                  
"AFG" "Other Manufacturing"                                  
"AFG" "Recycling"                                            
"AFG" "Electricity, Gas and Water"                          
"AFG" "Construction"                                        
"AFG" "Maintenance and Repair"                              
"AFG" "Wholesale Trade"                                      
"AFG" "Retail Trade"                                        
"AFG" "Hotels and Restraurants"                              
"AFG" "Transport"                                            
"AFG" "Post and Telecommunications"                          
"AFG" "Finacial Intermediation and Business Activities"      
"AFG" "Public Administration"                                
"AFG" "Education, Health and Other Services"                
"AFG" "Private Households"                                  
"AFG" "Others"                                              
"AFG" "Re-export & Re-import"                                
"ALB" "Agriculture"                                          
"ALB" "Fishing"                                              
"ALB" "Mining and Quarrying"                                
"ALB" "Food & Beverages"                                    
"ALB" "Textiles and Wearing Apparel"                        
"ALB" "Wood and Paper"                                      
"ALB" "Petroleum, Chemical and Non-Metallic Mineral Products"
"ALB" "Metal Products"                                      
"ALB" "Electrical and Machinery"                            
"ALB" "Transport Equipment"                                  
"ALB" "Other Manufacturing"                                  
"ALB" "Recycling"                                            
"ALB" "Electricity, Gas and Water"                          
"ALB" "Construction"                                        
"ALB" "Maintenance and Repair"                              
"ALB" "Wholesale Trade"                                      
"ALB" "Retail Trade"                                        
"ALB" "Hotels and Restraurants"                              
"ALB" "Transport"                                            
"ALB" "Post and Telecommunications"                          
"ALB" "Finacial Intermediation and Business Activities"      
"ALB" "Public Administration"                                
"ALB" "Education, Health and Other Services"                
"ALB" "Private Households"                                  
"ALB" "Others"                                              
"ALB" "Re-export & Re-import"                                
"DZA" "Agriculture"                                          
"DZA" "Fishing"                                              
"DZA" "Mining and Quarrying"                                
"DZA" "Food & Beverages"                                    
"DZA" "Textiles and Wearing Apparel"                        
"DZA" "Wood and Paper"                                      
"DZA" "Petroleum, Chemical and Non-Metallic Mineral Products"
"DZA" "Metal Products"                                      
"DZA" "Electrical and Machinery"                            
"DZA" "Transport Equipment"                                  
"DZA" "Other Manufacturing"                                  
"DZA" "Recycling"                                            
"DZA" "Electricity, Gas and Water"                          
"DZA" "Construction"                                        
"DZA" "Maintenance and Repair"                              
"DZA" "Wholesale Trade"                                      
"DZA" "Retail Trade"                                        
"DZA" "Hotels and Restraurants"                              
"DZA" "Transport"                                            
"DZA" "Post and Telecommunications"                          
"DZA" "Finacial Intermediation and Business Activities"      
"DZA" "Public Administration"                                
"DZA" "Education, Health and Other Services"                
"DZA" "Private Households"                                  
"DZA" "Others"                                              
"DZA" "Re-export & Re-import"                                  
end

    this is the data (1140 variables: v1, v2, ..., v1140) and 4915 rows:

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input float(v1 v2 v3 v4 v5 v6)
2441240 21191.4 4170.41 17477.9  9921.32 50.2978
97565.2 526.749 2603.48 7.44061  793.118 7.44061
  19237 18.9912 334.611 548.321   1452.7 1.78847
 835832 11606.1 1.04009  .44913  1812.68  .44913
 139399 1849.85 28.0308 5638.92  329.139 13.1474
68743.9 787.625 868.118 1167.19  366.212 3.00006
 371059 6791.24 2046.02  3304.2  524.962 7.82727
13225.7 206.618 769.938 14256.1  1183.06 32.5945
 270584 1967.64 54217.7  390631  2972.24 882.821
 291903 5520.08 46216.2  146178   5209.5 330.657
97185.7 1894.98 6587.47 28437.4  494.439   64.62
73768.7  2155.2 1033.51 .538108  1562.87 .538108
 299328 4600.22  .99417 .423813  .971799 .423813
14542.8  435.95  208382 1672080 .0774904 3778.87
62764.3 1068.48 253.104 3536.63  25.5146 8.40533
 642447 9522.87 10400.8  115235  1325.97 260.739
1309660 23103.9 596.696 28811.1 .0462178 65.3897
 918698   11991 2.57996 .389902 .0436335 .389902
1446430 16449.4 22308.1 56235.3  473.638 128.391
1594620 30724.4 16921.3  236435  35.4781 535.981
1815700 33046.1 20520.1  116286  2013.56 262.799
39257.8 877.134 1453220  305671  .044235 690.873
1579490   35195  638839 45887.5  2.15903 103.857
  58569  1683.6 1591.36 .492975 .0546335 .492975
52207.8 1484.11 .973791 .412943 .0486305 .412943
1.97724 1.97724 2.90604 1.33429  .118834 1.33429
15.3475 15.3475 17.7033 12.9699  1.90189 12.9699
 5.9218  5.9218 11.6035 6.64328   1.0927 6.64328
4.79997 4.79997 5.73331  4.2155   .86653  4.2155
4.89793 4.89793 6.06631 4.32443  .844162 4.32443
    2.9     2.9 4.26796 3.16776  .731749 3.16776
4.28543 4.28543  4.7724 3.78663  .784062 3.78663
4.40636 4.40636 5.38945 3.86041  .776238 3.86041
178.443 76.8288  7.1705 38.8116  .789073 38.8116
4.35186 4.35186 5.26654 3.73347  .753741 3.73347
2.70949 2.70949 5.14449 3.12221  .649052 3.12221
3.58353 3.58353 4.58135 3.21184  .682121 3.21184
4.79107 4.79107 6.16676 4.11525  .871956 4.11525
 4.6263  4.6263 9.13885 5.43886  .976056 5.43886
5.86666 5.86666 7.18465 5.20127  .994245 5.20127
4.66567 4.66567  5.5028 4.06843  .828575 4.06843
5.17145 5.17145 5.91564 4.44681  .849817 4.44681
5.39232 5.39232 6.30746 4.54631   .88587 4.54631
5.68984 5.29032 5.92829 4.69944  .911774 4.69944
4.14628 4.00148 4.06054 3.72311  .741343 3.72311
4.67004 4.66829 5.07511 4.05034  .823176 4.05034
  .4377   .4377 .774449 .493252    .1383 .493252
3.59708 3.59708 4.04101 3.04485  .662861 3.04485
3.80249 3.78845 4.55117 3.31362  .730224 3.31362
3.89579 3.89579  4.7169 3.39314  .747221 3.39314
.354392 .354392 .606728  .41291   .12939  .41291
 1.5645  1.5645 2.27563 1.71647  .511141 1.71647
5.66327 5.66327 6.99101  4.9518  .997859  4.9518
3.90806 3.90806 5.83796 3.73448  .800891 3.73448
2.14776 2.14776 3.76733 2.43089  .562836 2.43089
1.29387 1.29387 1.68445 1.27122   .33628 1.27122
1.03305 1.03305 1.33388 1.02454  .291758 1.02454
.796175 .796175 1.08551 .790788  .239891 .790788
 1.0255  1.0255 1.51122 1.09136  .325057 1.09136
1.02585 1.02585 1.39636 1.01266  .311863 1.01266
.912782 .912782 1.16316 .910454  .280202 .910454
  .7667   .7667 1.17776 .858698  .279151 .858698
.330883 .330883 .561324 .358035  .125904 .358035
.855947 .855947  1.4495 1.05161  .318898 1.05161
.747734 .747734 1.34881 .781753  .219156 .781753
1.32239 1.32239 1.88828 1.24866  .346614 1.24866
1.37681 1.37681 1.89884 1.33329  .343924 1.33329
1.35389 1.35389 1.78292 1.24642  .323104 1.24642
1.48574 1.48574 1.95581 1.44488   .36717 1.44488
1.48771 1.48771 1.99708 1.44017  .363102 1.44017
1.58998 1.58998 2.32287 1.54106  .403834 1.54106
1.58599 1.58599 1.98953 1.46313  .360765 1.46313
.151102 .151102 .242247  .17832 .0758672  .17832
.616322 .616322 .787826 .637047  .231692 .637047
.628639 .628639 .888718  .69533  .251808  .69533
  1.049   1.049 1.39573 1.09795  .323998 1.09795
.256474 .256474   .4417 .282369  .095939 .282369
.854724 .854724 1.40422 .803937  .223424 .803937
.853797 .853797 1.13894  .81816  .582412  .81816
 .69298  .69298 .930646 .753121  .574083 .753121
.697599 .697599 .939544 .746649  .578101 .746649
2.99739 2.99739  3.0225 2.94672  1.78535 2.94672
1.48777 1.48777 1.29092 1.15547  .775573 1.15547
2.46218 2.46218 2.04452 1.97054  1.22274 1.97054
4.03095 4.03095 3.31184 3.41223  1.98655 3.41223
3.33423 3.33423 2.97638 3.05017  1.80617 3.05017
3.07522 3.07522 2.74781 2.78055  1.67677 2.78055
  2.871   2.871 2.94499  2.8478  1.74704  2.8478
2.19119 2.19119 2.01598 1.91876  1.21101 1.91876
2.39588 2.39588   2.305 2.20123  1.36102 2.20123
.706408 .706408 .952106 .751883   .58445 .751883
4.60111 4.60111 3.70762 3.89785  2.24244 3.89785
3.98722 3.98722 3.21193 3.42635   1.9626 3.42635
4.69949 4.69949 3.82548 3.99717  2.31711 3.99717
5.01131 5.01131 4.01161 4.26481  2.42961 4.26481
4.82645 4.82645 3.91551 4.17882   2.4058 4.17882
 4.7585  4.7585 3.87448 4.08736  2.28331 4.08736
4.58152 4.58152  3.7486  3.8981  2.21363  3.8981
   .685    .685 .912969 .716466  .556091 .716466
4.21333 4.21333 3.45107 3.62863  2.11547 3.62863
end

    This is a simplified example of how the matrix eventually should look like if we combine the three files altogether (simplified example with 2 countries, 2 sectors, 2 final demand categories):
    AFG AFG ALB ALB
    Household final consumption P.3h Non-profit institutions serving households P.3n Household final consumption P.3h Non-profit institutions serving households P.3n
    AFG Agriculture 2441240.00 21191.40 1.37 1.34
    AFG Fishing 97565.20 526.75 0.21 0.21
    ALB Agriculture 15.35 15.35 479448.00 327.81
    ALB Fishing 5.92 5.92 23296.70 10.66

    I would like to combine these three files in a Stata friendly format that would look like this:
    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input str3 country str53 sector str47 finaldemandcategory double finaldemand
"AFG" "Agriculture"                                           "Household final consumption P.3h"                2440000
"AFG" "Fishing"                                               "Household final consumption P.3h"                  97600
"AFG" "Mining and Quarrying"                                  "Household final consumption P.3h"                  19200
"AFG" "Food & Beverages"                                      "Household final consumption P.3h"                 836000
"AFG" "Textiles and Wearing Apparel"                          "Household final consumption P.3h"                 139000
"AFG" "Wood and Paper"                                        "Household final consumption P.3h"                  68700
"AFG" "Petroleum, Chemical and Non-Metallic Mineral Products" "Household final consumption P.3h"                 371000
"AFG" "Metal Products"                                        "Household final consumption P.3h"                  13200
"AFG" "Electrical and Machinery"                              "Household final consumption P.3h"                 271000
"AFG" "Transport Equipment"                                   "Household final consumption P.3h"                 292000
"AFG" "Other Manufacturing"                                   "Household final consumption P.3h"                  97200
"AFG" "Recycling"                                             "Household final consumption P.3h"                  73800
"AFG" "Electricity, Gas and Water"                            "Household final consumption P.3h"                 299000
"AFG" "Construction"                                          "Household final consumption P.3h"                  14500
"AFG" "Maintenance and Repair"                                "Household final consumption P.3h"                  62800
"AFG" "Wholesale Trade"                                       "Household final consumption P.3h"                 642000
"AFG" "Retail Trade"                                          "Household final consumption P.3h"                1310000
"AFG" "Hotels and Restraurants"                               "Household final consumption P.3h"                 919000
"AFG" "Transport"                                             "Household final consumption P.3h"                1450000
"AFG" "Post and Telecommunications"                           "Household final consumption P.3h"                1590000
"AFG" "Finacial Intermediation and Business Activities"       "Household final consumption P.3h"                1820000
"AFG" "Public Administration"                                 "Household final consumption P.3h"                  39300
"AFG" "Education, Health and Other Services"                  "Household final consumption P.3h"                1580000
"AFG" "Private Households"                                    "Household final consumption P.3h"                  58600
"AFG" "Others"                                                "Household final consumption P.3h"                  52200
"AFG" "Re-export & Re-import"                                 "Household final consumption P.3h"                   1.98
"AFG" "Agriculture"                                           "Non-profit institutions serving households P.3n"   21200
"AFG" "Fishing"                                               "Non-profit institutions serving households P.3n"     527
"AFG" "Mining and Quarrying"                                  "Non-profit institutions serving households P.3n"      19
"AFG" "Food & Beverages"                                      "Non-profit institutions serving households P.3n"   11600
"AFG" "Textiles and Wearing Apparel"                          "Non-profit institutions serving households P.3n"    1850
"AFG" "Wood and Paper"                                        "Non-profit institutions serving households P.3n"     788
"AFG" "Petroleum, Chemical and Non-Metallic Mineral Products" "Non-profit institutions serving households P.3n"    6790
"AFG" "Metal Products"                                        "Non-profit institutions serving households P.3n"     207
"AFG" "Electrical and Machinery"                              "Non-profit institutions serving households P.3n"    1970
"AFG" "Transport Equipment"                                   "Non-profit institutions serving households P.3n"    5520
"AFG" "Other Manufacturing"                                   "Non-profit institutions serving households P.3n"    1890
"AFG" "Recycling"                                             "Non-profit institutions serving households P.3n"    2160
"AFG" "Electricity, Gas and Water"                            "Non-profit institutions serving households P.3n"    4600
"AFG" "Construction"                                          "Non-profit institutions serving households P.3n"     436
"AFG" "Maintenance and Repair"                                "Non-profit institutions serving households P.3n"    1070
"AFG" "Wholesale Trade"                                       "Non-profit institutions serving households P.3n"    9520
"AFG" "Retail Trade"                                          "Non-profit institutions serving households P.3n"   23100
"AFG" "Hotels and Restraurants"                               "Non-profit institutions serving households P.3n"   12000
"AFG" "Transport"                                             "Non-profit institutions serving households P.3n"   16400
"AFG" "Post and Telecommunications"                           "Non-profit institutions serving households P.3n"   30700
"AFG" "Finacial Intermediation and Business Activities"       "Non-profit institutions serving households P.3n"   33000
"AFG" "Public Administration"                                 "Non-profit institutions serving households P.3n"     877
"AFG" "Education, Health and Other Services"                  "Non-profit institutions serving households P.3n"   35200
"AFG" "Private Households"                                    "Non-profit institutions serving households P.3n"    1680
"AFG" "Others"                                                "Non-profit institutions serving households P.3n"    1480
"AFG" "Re-export & Re-import"                                 "Non-profit institutions serving households P.3n"    1.98
"AFG" "Agriculture"                                           "Government final consumption P.3g"                  4170
"AFG" "Fishing"                                               "Government final consumption P.3g"                  2600
"AFG" "Mining and Quarrying"                                  "Government final consumption P.3g"                   335
"AFG" "Food & Beverages"                                      "Government final consumption P.3g"                  1.04
"AFG" "Textiles and Wearing Apparel"                          "Government final consumption P.3g"                    28
"AFG" "Wood and Paper"                                        "Government final consumption P.3g"                   868
"AFG" "Petroleum, Chemical and Non-Metallic Mineral Products" "Government final consumption P.3g"                  2050
"AFG" "Metal Products"                                        "Government final consumption P.3g"                   770
"AFG" "Electrical and Machinery"                              "Government final consumption P.3g"                 54200
"AFG" "Transport Equipment"                                   "Government final consumption P.3g"                 46200
"AFG" "Other Manufacturing"                                   "Government final consumption P.3g"                  6590
"AFG" "Recycling"                                             "Government final consumption P.3g"                  1030
"AFG" "Electricity, Gas and Water"                            "Government final consumption P.3g"                  .994
"AFG" "Construction"                                          "Government final consumption P.3g"                208000
"AFG" "Maintenance and Repair"                                "Government final consumption P.3g"                   253
"AFG" "Wholesale Trade"                                       "Government final consumption P.3g"                 10400
"AFG" "Retail Trade"                                          "Government final consumption P.3g"                   597
"AFG" "Hotels and Restraurants"                               "Government final consumption P.3g"                  2.58
"AFG" "Transport"                                             "Government final consumption P.3g"                 22300
"AFG" "Post and Telecommunications"                           "Government final consumption P.3g"                 16900
"AFG" "Finacial Intermediation and Business Activities"       "Government final consumption P.3g"                 20500
"AFG" "Public Administration"                                 "Government final consumption P.3g"               1450000
"AFG" "Education, Health and Other Services"                  "Government final consumption P.3g"                639000
"AFG" "Private Households"                                    "Government final consumption P.3g"                  1590
"AFG" "Others"                                                "Government final consumption P.3g"                  .974
"AFG" "Re-export & Re-import"                                 "Government final consumption P.3g"                  2.91
"AFG" "Agriculture"                                           "Gross fixed capital formation P.51"                17500
"AFG" "Fishing"                                               "Gross fixed capital formation P.51"                 7.44
"AFG" "Mining and Quarrying"                                  "Gross fixed capital formation P.51"                  548
"AFG" "Food & Beverages"                                      "Gross fixed capital formation P.51"                 .449
"AFG" "Textiles and Wearing Apparel"                          "Gross fixed capital formation P.51"                 5640
"AFG" "Wood and Paper"                                        "Gross fixed capital formation P.51"                 1170
"AFG" "Petroleum, Chemical and Non-Metallic Mineral Products" "Gross fixed capital formation P.51"                 3300
"AFG" "Metal Products"                                        "Gross fixed capital formation P.51"                14300
"AFG" "Electrical and Machinery"                              "Gross fixed capital formation P.51"               391000
"AFG" "Transport Equipment"                                   "Gross fixed capital formation P.51"               146000
"AFG" "Other Manufacturing"                                   "Gross fixed capital formation P.51"                28400
"AFG" "Recycling"                                             "Gross fixed capital formation P.51"                 .538
"AFG" "Electricity, Gas and Water"                            "Gross fixed capital formation P.51"                 .424
"AFG" "Construction"                                          "Gross fixed capital formation P.51"              1670000
"AFG" "Maintenance and Repair"                                "Gross fixed capital formation P.51"                 3540
"AFG" "Wholesale Trade"                                       "Gross fixed capital formation P.51"               115000
"AFG" "Retail Trade"                                          "Gross fixed capital formation P.51"                28800
"AFG" "Hotels and Restraurants"                               "Gross fixed capital formation P.51"                  .39
"AFG" "Transport"                                             "Gross fixed capital formation P.51"                56200
"AFG" "Post and Telecommunications"                           "Gross fixed capital formation P.51"               236000
"AFG" "Finacial Intermediation and Business Activities"       "Gross fixed capital formation P.51"               116000
"AFG" "Public Administration"                                 "Gross fixed capital formation P.51"               306000
end

    Many thanks in advance.

    Jala

    Tags: None
  • #2
    You put in a lot of effort to present an example, much more than most people, and I applaud you for that. However, I still find it hard to understand how to work with what you present, even though I have a passing acquaintance with input/output analysis.

    You would increase your chances of getting an answer if you were to:
    1) Use variable names that mean something rather than v1, v2, ... .
    2) Use different variable names for different things in different files.
    3) Present a desired example of your results using numbers that come from the input data. ( I can't, for example, know where the 2440000 comes from in your data files,since it appears nowhere in your input data.)
    4) Present the shortest possible result file example consistent with the input data.

    Last edited by Mike Lacy; 14 Apr 2023, 14:59.

    Comment

    • #3
      Thanks a lot Mike Lacy for your kind words and for your useful guidance on how to make my questions easier to understand. This is much appreciated at my end.

      I am sorry if my earlier post was confusing. I also realized there was a missing detail in what I previously presented.

      I will present a simplified exam with two countries (AFG and ALB), two sectors (Agriculture and Fishing) and two final demand categories (Household final consumption and Government final consumption). This is how the matrix should look like (this is not a Stata format, this is just for the sake of explaining the data).
      AFG AFG ALB ALB
      country sector Household final consumption Government final consumption Household final consumption Government final consumption
      AFG Agriculture 2441240 4170.41 1.36908 2.23576
      AFG Fishing 97565.2 2603.48 0.210492 0.244429
      ALB Agriculture 15.3475 17.7033 479448 1023.21
      ALB Fishing 5.9218 11.6035 23296.7 594.453

      The data comes in three separate .txt files, two for the labels and one for the data itself.

      The first .txt file includes the labels of countries and final demand categories. Please find below an example:

      Code:
      * Example generated by -dataex-. For more info, type help dataex
clear
input str3 country str28 finaldemand
"AFG" "Household final consumption"
"AFG" "Government final consumption"
"ALB" "Household final consumption"
"ALB" "Government final consumption"
end

      The second .txt file includes the labels of countries and sectors. Please find below an example:

      Code:
      * Example generated by -dataex-. For more info, type help dataex
clear
input str3 country str11 sector
"AFG" "Agriculture"
"AFG" "Fishing"    
"ALB" "Agriculture"
"ALB" "Fishing"    
end

      The third .txt file includes the data itself. Please find below an example:

      Code:
      * Example generated by -dataex-. For more info, type help dataex
clear
input double v1 float(v2 v3 v4)
2441240 4170.41 1.36908 2.23576
97565.2 2603.48 .210492 .244429
15.3475 17.7033  479448 1023.21
 5.9218 11.6035 23296.7 594.453
end

      I would like to combine these three files altogether in order to be able to produce an output like the one below. I would be grateful if you could advise.

      Code:
      * Example generated by -dataex-. For more info, type help dataex
clear
input str3 country str11 sector str28 finaldemand str3 countrydestination double finaldemandvalue
"AFG" "Agriculture" "Household final consumption"  "AFG" 2441240
"AFG" "Fishing"     "Household final consumption"  "AFG" 97565.2
"AFG" "Agriculture" "Household final consumption"  "ALB" 1.36908
"AFG" "Fishing"     "Household final consumption"  "ALB" .210492
"AFG" "Agriculture" "Government final consumption" "AFG" 4170.41
"AFG" "Fishing"     "Government final consumption" "AFG" 2603.48
"AFG" "Agriculture" "Government final consumption" "ALB" 2.23576
"AFG" "Fishing"     "Government final consumption" "ALB" .244429
"ALB" "Agriculture" "Household final consumption"  "AFG" 15.3475
"ALB" "Fishing"     "Household final consumption"  "AFG"  5.9218
"ALB" "Agriculture" "Household final consumption"  "ALB"  479448
"ALB" "Fishing"     "Household final consumption"  "ALB" 23296.7
"ALB" "Agriculture" "Government final consumption" "AFG" 17.7033
"ALB" "Fishing"     "Government final consumption" "AFG" 11.6035
"ALB" "Agriculture" "Government final consumption" "ALB" 1023.21
"ALB" "Fishing"     "Government final consumption" "ALB" 594.453
end

      Many thanks in advance for your help.

      Jala

      Comment

      • #4
        This is a complete mess. For the final dataset, how do you know what value corresponds to what country, sector and category? Any dataset that relies on order is inherently dangerous as extra observations, missing observations or a misunderstanding of the data will result in errors. You can build on the following after being warned.

        Code:
        * Example generated by -dataex-. For more info, type help dataex
clear
input str3 country str28 finaldemand
"AFG" "Household final consumption"
"AFG" "Government final consumption"
"ALB" "Household final consumption"
"ALB" "Government final consumption"
end

isid country finaldemand
tempfile holding
save `holding'

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 country str11 sector
"AFG" "Agriculture"
"AFG" "Fishing"    
"ALB" "Agriculture"
"ALB" "Fishing"    
end

isid country sector
cross using `holding'
bys country finaldemand sector: gen order=_n
gsort country -finaldemand order sector
drop order
save `holding', replace
qui levelsof country, local(countries) clean


* Example generated by -dataex-. For more info, type help dataex
clear
input double v1 float(v2 v3 v4)
2441240 4170.41 1.36908 2.23576
97565.2 2603.48 .210492 .244429
15.3475 17.7033  479448 1023.21
 5.9218 11.6035 23296.7 594.453
end
egen ncountry= seq(), block(`=wordcount("`countries'")')

forval i=1/`=c(k)'{
    if mod(`i', 2){
        cap rename v`i' hh`i'
    }
}
rename hh# hh#, addnumber(1)
rename v# gov#, addnumber(1)
gen long obsno=_n
reshape long hh gov, i(obsno) j(seq)
rename (hh gov) value=
reshape long value, i(obsno seq) j(which) string
gsort ncountry -which  seq obsno
merge 1:1 _n using `holding', nogen
encode country, g(label)
label values ncountry label
label values seq label
rename (ncountry seq) (home destination )
keep home destination sector finaldemand value
order home destination sector finaldemand value
        Res.:

        Code:
        . l, sep(0)

     +------------------------------------------------------------------------+
     | home   destin~n        sector                    finaldemand     value |
     |------------------------------------------------------------------------|
  1. |  AFG        AFG   Agriculture    Household final consumption   2441240 |
  2. |  AFG        AFG       Fishing    Household final consumption   97565.2 |
  3. |  AFG        ALB   Agriculture    Household final consumption   1.36908 |
  4. |  AFG        ALB       Fishing    Household final consumption   .210492 |
  5. |  AFG        AFG   Agriculture   Government final consumption   4170.41 |
  6. |  AFG        AFG       Fishing   Government final consumption   2603.48 |
  7. |  AFG        ALB   Agriculture   Government final consumption   2.23576 |
  8. |  AFG        ALB       Fishing   Government final consumption   .244429 |
  9. |  ALB        AFG   Agriculture    Household final consumption   15.3475 |
 10. |  ALB        AFG       Fishing    Household final consumption    5.9218 |
 11. |  ALB        ALB   Agriculture    Household final consumption    479448 |
 12. |  ALB        ALB       Fishing    Household final consumption   23296.7 |
 13. |  ALB        AFG   Agriculture   Government final consumption   17.7033 |
 14. |  ALB        AFG       Fishing   Government final consumption   11.6035 |
 15. |  ALB        ALB   Agriculture   Government final consumption   1023.21 |
 16. |  ALB        ALB       Fishing   Government final consumption   594.453 |
     +------------------------------------------------------------------------+
        • 1 like

        Comment

        Previous Next
        Working...
        X