Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Reshaping data

    Hello,

    I need help in wrangling some data on Stata. Can someone share the code for the below operation?

    1. I would like to have the years in a single column (long data) , starting with 1990 for all observations, then 1991, then 1992, etc.
    2. I would like for "Country" to remain in a column but arranged in alphabetical order ( Inida, Italie , Japon, Korea, UK, USA)
    3 Finally, I would like each category to be in a separate column.

    The file I need help for is attached (DATAEXAMPLE1), and I have also attached a picture of the format I hope to get (Objective).

    Thank you
    Attached Files

  • #2
    As a general rule, many here do not like downloading .xlsx files from web-forums like this because they pose a security risk. Could you please generate a data example using the -dataex- command, then paste the example into a new post? You should also surround the data example with CODE tags (see the # symbol in the editor).

    Comment


    • #3
      Thank you.

      Here is my data:


      Code:
      * Example generated by -dataex-. For more info, type help dataex
      clear
      input str6 Country str1 Category double(YEAR1990 YEAR1991 YEAR1992) byte(YEAR1993 YEAR1994) int YEAR1995 byte(YEAR1996 YEAR1997) int YEAR1998 double YEAR1999
      "Japan " "A"                54                42               14 86 92   90 -21 90    71    8
      "Japan " "B"                 3                40               12 84 90   88 -20 88    54  8.3
      "Japan " "C"                65                38 9.99999999999999 82 88   86 -19 86     5  8.6
      "Japan " "D"                34                36 7.99999999999999 80 86 2770 -18 84     4  8.9
      "Japan " "E"                45                 3                6 78 84 2703 -17 82   -29  9.2
      "Japan " "F"                 3                65                4 76 82 2636 -16 80   -54  9.5
      "Japan " "G"               434                34                2 74 80 2569 -15 78   -79   12
      "Japan " "H"                43                45                0 72 78 2502 -14 76  -104    8
      "USA"    "A"                54                 3               -2 70 76 2435 -13 74  -129    8
      "USA"    "B"                76                24               -4 68 74 2368 -12 72  -154   13
      "USA"    "C"                45                22               -6 66 72 2301 -11 70  -179   11
      "USA"    "D"                 4                20               -8 64 70 2234 -10 68  -204 11.3
      "USA"    "E"                 3                18              -10 62 68 2167  -9 66  -229 11.6
      "USA"    "F"                54                16              -12 60 66 2100  -8 64  -254 11.9
      "USA"    "G"  13.3333333333333                14              -14 58 64 2033  -7 62  -279 12.2
      "USA"    "H"  5.90476190476191                12              -16 56 62 1966  -6 60  -304 12.5
      "KOREA " "A" -1.52380952380949  9.99999999999999              -18 54 60 1899  -5 58  -329 12.8
      "KOREA " "B" -8.95238095238099  7.99999999999999              -20 52 58 1832  -4 56  -354 13.1
      "KOREA " "C" -16.3809523809524  5.99999999999999              -22 50 56 1765  -3 54  -379 13.4
      "KOREA " "D" -23.8095238095238  3.99999999999999              -24 48 54 1698  -2 52  -404 13.7
      "KOREA " "E" -31.2380952380952  1.99999999999999              -26 46 52 1631  -1 50  -429   14
      "KOREA " "F" -38.6666666666666                67              -28 44 50 1564   0 48  -454 14.3
      "KOREA " "G"  -46.095238095238 -1.99999999999991              -30 42 48 1497   1 46  -479 14.6
      "KOREA " "H" -53.5238095238094 -3.99999999999991              -32 40 46 1430   2 44  -504 14.9
      "INDIA"  "A" -60.9523809523808 -5.99999999999991              -34 38 44 1363   3 42  -529 15.2
      "INDIA"  "B" -68.3809523809522 -7.99999999999991              -36 36 42 1296   4 40  -554 15.5
      "INDIA"  "C" -75.8095238095236 -9.99999999999991              -38 34 40 1229   5 38  -579 15.8
      "INDIA"  "D"  -83.238095238095 -11.9999999999999              -40 32 38 1162   6 36  -604 16.1
      "INDIA"  "E" -90.6666666666664 -13.9999999999999              -42 30 36 1095   7 34  -629 16.4
      "INDIA"  "F" -98.0952380952378 -15.9999999999999              -44 28 34 1028   8 32  -654 16.7
      "INDIA"  "G" -105.523809523809 -17.9999999999999              -46 26 32  961  30 30  -679   17
      "INDIA"  "H" -112.952380952381 -19.9999999999999              -48 24 30  894  28 28  -704 17.3
      "UK"     "A" -120.380952380952 -21.9999999999999              -50 22 28  827  26 26  -729 17.6
      "UK"     "B" -127.809523809524 -23.9999999999999              -52 20 26  760  24 24  -754 17.9
      "UK"     "C" -135.238095238095 -25.9999999999999              -54 18 24  693  22 22  -779 18.2
      "UK"     "D" -142.666666666666 -27.9999999999999              -56 16 22  626  20 20  -804 18.5
      "UK"     "E" -150.095238095238 -29.9999999999999              -58 14 20  559  18 18  -829 18.8
      "UK"     "F" -157.523809523809 -31.9999999999999              -60 12 18  492  16 16  -854 19.1
      "UK"     "G" -164.952380952381 -33.9999999999999              -62 10 16  425  14 14  -879 19.4
      "UK"     "H" -172.380952380952 -35.9999999999999              -64  8 14  358  12 12  -904 19.7
      "ITALIE" "A" -179.809523809523 -37.9999999999999              -66  6 12  291  10 10  -929   20
      "ITALIE" "B" -187.238095238095 -39.9999999999999              -68  4 10  224   8  8  -954 20.3
      "ITALIE" "C" -194.666666666666 -41.9999999999999              -70  2  8  157   6  6  -979 20.6
      "ITALIE" "D" -202.095238095238 -43.9999999999999              -72  0  6   90   4  4 -1004 20.9
      "ITALIE" "E" -209.523809523809 -45.9999999999999              -74 -2  4   23   2  2 -1029 21.2
      "ITALIE" "F"  -216.95238095238 -47.9999999999999              -76 -4  2    0   0  0 -1054 21.5
      "ITALIE" "G" -224.380952380952 -49.9999999999999              -78 -6  0   -2  -2 -2 -1079 21.8
      "ITALIE" "H" -231.809523809523 -51.9999999999999              -80 -8 -2   -4  -4 -4 -1104 22.1
      end

      Comment


      • #4
        Code:
        reshape long YEAR, i(Country Category) j(Year)
        reshape wide YEAR, i(Country Year) j(Category) string
        rename YEAR* *
        Res.:

        Code:
        . l, sep(0)
        
             +------------------------------------------------------------------------------------------------------------------------+
             | Country   Year            A            B            C            D            E            F            G            H |
             |------------------------------------------------------------------------------------------------------------------------|
          1. |   INDIA   1990   -60.952381   -68.380952   -75.809524   -83.238095   -90.666667   -98.095238   -105.52381   -112.95238 |
          2. |   INDIA   1991           -6           -8          -10          -12          -14          -16          -18          -20 |
          3. |   INDIA   1992          -34          -36          -38          -40          -42          -44          -46          -48 |
          4. |   INDIA   1993           38           36           34           32           30           28           26           24 |
          5. |   INDIA   1994           44           42           40           38           36           34           32           30 |
          6. |   INDIA   1995         1363         1296         1229         1162         1095         1028          961          894 |
          7. |   INDIA   1996            3            4            5            6            7            8           30           28 |
          8. |   INDIA   1997           42           40           38           36           34           32           30           28 |
          9. |   INDIA   1998         -529         -554         -579         -604         -629         -654         -679         -704 |
         10. |   INDIA   1999         15.2         15.5         15.8         16.1         16.4         16.7           17         17.3 |
         11. |  ITALIE   1990   -179.80952    -187.2381   -194.66667   -202.09524   -209.52381   -216.95238   -224.38095   -231.80952 |
         12. |  ITALIE   1991          -38          -40          -42          -44          -46          -48          -50          -52 |
         13. |  ITALIE   1992          -66          -68          -70          -72          -74          -76          -78          -80 |
         14. |  ITALIE   1993            6            4            2            0           -2           -4           -6           -8 |
         15. |  ITALIE   1994           12           10            8            6            4            2            0           -2 |
         16. |  ITALIE   1995          291          224          157           90           23            0           -2           -4 |
         17. |  ITALIE   1996           10            8            6            4            2            0           -2           -4 |
         18. |  ITALIE   1997           10            8            6            4            2            0           -2           -4 |
         19. |  ITALIE   1998         -929         -954         -979        -1004        -1029        -1054        -1079        -1104 |
         20. |  ITALIE   1999           20         20.3         20.6         20.9         21.2         21.5         21.8         22.1 |
         21. |  Japan    1990           54            3           65           34           45            3          434           43 |
         22. |  Japan    1991           42           40           38           36            3           65           34           45 |
         23. |  Japan    1992           14           12           10            8            6            4            2            0 |
         24. |  Japan    1993           86           84           82           80           78           76           74           72 |
         25. |  Japan    1994           92           90           88           86           84           82           80           78 |
         26. |  Japan    1995           90           88           86         2770         2703         2636         2569         2502 |
         27. |  Japan    1996          -21          -20          -19          -18          -17          -16          -15          -14 |
         28. |  Japan    1997           90           88           86           84           82           80           78           76 |
         29. |  Japan    1998           71           54            5            4          -29          -54          -79         -104 |
         30. |  Japan    1999            8          8.3          8.6          8.9          9.2          9.5           12            8 |
         31. |  KOREA    1990   -1.5238095    -8.952381   -16.380952   -23.809524   -31.238095   -38.666667   -46.095238    -53.52381 |
         32. |  KOREA    1991           10            8            6            4            2           67           -2           -4 |
         33. |  KOREA    1992          -18          -20          -22          -24          -26          -28          -30          -32 |
         34. |  KOREA    1993           54           52           50           48           46           44           42           40 |
         35. |  KOREA    1994           60           58           56           54           52           50           48           46 |
         36. |  KOREA    1995         1899         1832         1765         1698         1631         1564         1497         1430 |
         37. |  KOREA    1996           -5           -4           -3           -2           -1            0            1            2 |
         38. |  KOREA    1997           58           56           54           52           50           48           46           44 |
         39. |  KOREA    1998         -329         -354         -379         -404         -429         -454         -479         -504 |
         40. |  KOREA    1999         12.8         13.1         13.4         13.7           14         14.3         14.6         14.9 |
         41. |      UK   1990   -120.38095   -127.80952    -135.2381   -142.66667   -150.09524   -157.52381   -164.95238   -172.38095 |
         42. |      UK   1991          -22          -24          -26          -28          -30          -32          -34          -36 |
         43. |      UK   1992          -50          -52          -54          -56          -58          -60          -62          -64 |
         44. |      UK   1993           22           20           18           16           14           12           10            8 |
         45. |      UK   1994           28           26           24           22           20           18           16           14 |
         46. |      UK   1995          827          760          693          626          559          492          425          358 |
         47. |      UK   1996           26           24           22           20           18           16           14           12 |
         48. |      UK   1997           26           24           22           20           18           16           14           12 |
         49. |      UK   1998         -729         -754         -779         -804         -829         -854         -879         -904 |
         50. |      UK   1999         17.6         17.9         18.2         18.5         18.8         19.1         19.4         19.7 |
         51. |     USA   1990           54           76           45            4            3           54    13.333333    5.9047619 |
         52. |     USA   1991            3           24           22           20           18           16           14           12 |
         53. |     USA   1992           -2           -4           -6           -8          -10          -12          -14          -16 |
         54. |     USA   1993           70           68           66           64           62           60           58           56 |
         55. |     USA   1994           76           74           72           70           68           66           64           62 |
         56. |     USA   1995         2435         2368         2301         2234         2167         2100         2033         1966 |
         57. |     USA   1996          -13          -12          -11          -10           -9           -8           -7           -6 |
         58. |     USA   1997           74           72           70           68           66           64           62           60 |
         59. |     USA   1998         -129         -154         -179         -204         -229         -254         -279         -304 |
         60. |     USA   1999            8           13           11         11.3         11.6         11.9         12.2         12.5 |
             +------------------------------------------------------------------------------------------------------------------------+
        
        .

        Comment


        • #5
          So you have data that is long in category and wide in year, but, sensibly, you want the reverse of that. That dance involves two -reshape- steps, plus a little bit of variable renaming:
          Code:
          rename YEAR* _*
          reshape long _, i(Country Category) j(year)
          reshape wide _, i(Country year) j(Category) string
          rename _* *
          Added: Crossed with #4.

          Comment


          • #6
            Thank you very much, this is really useful. I have a follow up question.
            I noticed that when there are spaces in the category variable, the code gives an error message.

            Can you please tell me what would be the correct way to code the below data (including spaces in the category variable)?


            Code:
            * Example generated by -dataex-. For more info, type help dataex
            clear
            input str6 Country str24 Category double(YEAR1990 YEAR1991 YEAR1992) byte(YEAR1993 YEAR1994) int YEAR1995 byte(YEAR1996 YEAR1997) int YEAR1998 double YEAR1999
            "Japan " "GDP Output"                              54                42               14 86 92   90 -21 90    71    8
            "Japan " "Growth rate: cont"                        3                40               12 84 90   88 -20 88    54  8.3
            "Japan " "GDP growth"                              65                38 9.99999999999999 82 88   86 -19 86     5  8.6
            "Japan " "Echange rate"                            34                36 7.99999999999999 80 86 2770 -18 84     4  8.9
            "Japan " "Real Estate: deffierence"                45                 3                6 78 84 2703 -17 82   -29  9.2
            "Japan " "Price/Cost "                              3                65                4 76 82 2636 -16 80   -54  9.5
            "Japan " "Public service/Admin"                   434                34                2 74 80 2569 -15 78   -79   12
            "Japan " "Curency"                                 43                45                0 72 78 2502 -14 76  -104    8
            "USA"    "GDP Output"                              54                 3               -2 70 76 2435 -13 74  -129    8
            "USA"    "Growth rate: cont"                       76                24               -4 68 74 2368 -12 72  -154   13
            "USA"    "GDP growth"                              45                22               -6 66 72 2301 -11 70  -179   11
            "USA"    "Echange rate"                             4                20               -8 64 70 2234 -10 68  -204 11.3
            "USA"    "Real Estate: deffierence"                 3                18              -10 62 68 2167  -9 66  -229 11.6
            "USA"    "Price/Cost "                             54                16              -12 60 66 2100  -8 64  -254 11.9
            "USA"    "Public service/Admin"      13.3333333333333                14              -14 58 64 2033  -7 62  -279 12.2
            "USA"    "Curency"                   5.90476190476191                12              -16 56 62 1966  -6 60  -304 12.5
            "KOREA " "GDP Output"               -1.52380952380949  9.99999999999999              -18 54 60 1899  -5 58  -329 12.8
            "KOREA " "Growth rate: cont"        -8.95238095238099  7.99999999999999              -20 52 58 1832  -4 56  -354 13.1
            "KOREA " "GDP growth"               -16.3809523809524  5.99999999999999              -22 50 56 1765  -3 54  -379 13.4
            "KOREA " "Echange rate"             -23.8095238095238  3.99999999999999              -24 48 54 1698  -2 52  -404 13.7
            "KOREA " "Real Estate: deffierence" -31.2380952380952  1.99999999999999              -26 46 52 1631  -1 50  -429   14
            "KOREA " "Price/Cost "              -38.6666666666666                67              -28 44 50 1564   0 48  -454 14.3
            "KOREA " "Public service/Admin"      -46.095238095238 -1.99999999999991              -30 42 48 1497   1 46  -479 14.6
            "KOREA " "Curency"                  -53.5238095238094 -3.99999999999991              -32 40 46 1430   2 44  -504 14.9
            "INDIA"  "GDP Output"               -60.9523809523808 -5.99999999999991              -34 38 44 1363   3 42  -529 15.2
            "INDIA"  "Growth rate: cont"        -68.3809523809522 -7.99999999999991              -36 36 42 1296   4 40  -554 15.5
            "INDIA"  "GDP growth"               -75.8095238095236 -9.99999999999991              -38 34 40 1229   5 38  -579 15.8
            "INDIA"  "Echange rate"              -83.238095238095 -11.9999999999999              -40 32 38 1162   6 36  -604 16.1
            "INDIA"  "Real Estate: deffierence" -90.6666666666664 -13.9999999999999              -42 30 36 1095   7 34  -629 16.4
            "INDIA"  "Price/Cost "              -98.0952380952378 -15.9999999999999              -44 28 34 1028   8 32  -654 16.7
            "INDIA"  "Public service/Admin"     -105.523809523809 -17.9999999999999              -46 26 32  961  30 30  -679   17
            "INDIA"  "Curency"                  -112.952380952381 -19.9999999999999              -48 24 30  894  28 28  -704 17.3
            "UK"     "GDP Output"               -120.380952380952 -21.9999999999999              -50 22 28  827  26 26  -729 17.6
            "UK"     "Growth rate: cont"        -127.809523809524 -23.9999999999999              -52 20 26  760  24 24  -754 17.9
            "UK"     "GDP growth"               -135.238095238095 -25.9999999999999              -54 18 24  693  22 22  -779 18.2
            "UK"     "Echange rate"             -142.666666666666 -27.9999999999999              -56 16 22  626  20 20  -804 18.5
            "UK"     "Real Estate: deffierence" -150.095238095238 -29.9999999999999              -58 14 20  559  18 18  -829 18.8
            "UK"     "Price/Cost "              -157.523809523809 -31.9999999999999              -60 12 18  492  16 16  -854 19.1
            "UK"     "Public service/Admin"     -164.952380952381 -33.9999999999999              -62 10 16  425  14 14  -879 19.4
            "UK"     "Curency"                  -172.380952380952 -35.9999999999999              -64  8 14  358  12 12  -904 19.7
            "ITALIE" "GDP Output"               -179.809523809523 -37.9999999999999              -66  6 12  291  10 10  -929   20
            "ITALIE" "Growth rate: cont"        -187.238095238095 -39.9999999999999              -68  4 10  224   8  8  -954 20.3
            "ITALIE" "GDP growth"               -194.666666666666 -41.9999999999999              -70  2  8  157   6  6  -979 20.6
            "ITALIE" "Echange rate"             -202.095238095238 -43.9999999999999              -72  0  6   90   4  4 -1004 20.9
            "ITALIE" "Real Estate: deffierence" -209.523809523809 -45.9999999999999              -74 -2  4   23   2  2 -1029 21.2
            "ITALIE" "Price/Cost "               -216.95238095238 -47.9999999999999              -76 -4  2    0   0  0 -1054 21.5
            "ITALIE" "Public service/Admin"     -224.380952380952 -49.9999999999999              -78 -6  0   -2  -2 -2 -1079 21.8
            "ITALIE" "Curency"                  -231.809523809523 -51.9999999999999              -80 -8 -2   -4  -4 -4 -1104 22.1
            end

            Comment


            • #7
              You can pass the strings through -strtoname()- to start with, but note Stata name length limits.

              Code:
              help strtoname()
              help naming conventions
              Code:
              replace Category= strtoname(Category)
              Last edited by Andrew Musau; 20 Jun 2023, 16:46.

              Comment


              • #8
                Just to clarify the situation, there are rules restricting what can be a Stata variable name. All of the characters must be letters of the English alphabet, or digits, or underscore (_) characters. In addition, the length cannot exceed 32 characters. So when you have a value of a string variable that contains blank spaces, that value cannot become a variable name and -reshape-, accordingly declines to proceed. The -strtoname()- function takes values of a string variable and edits them so as to turn them into legal variable names, although it does not enforce the 32 character limit. So if any of them exceed that limit, you will have to separately find a way to shorten those.
                Code:
                replace Category = strtoname(Category)
                levelsof Category if strlen(Category) > 32
                Sometimes just taking the first 32 characters is a good way to do it. But sometimes that leaves you with a variable name that is hard to understand, or shorn of meaningful information. So for those cases you have to figure out some other way to shorten it.

                Comment


                • #9
                  I was able to resolve the issue. Thank you for your help.

                  Comment


                  • #10
                    Hello,

                    I am trying to replicate the same operation with the following dataset (below), but I am getting a error message (variable CountryocdevariableNotesyear not found or invalid variable name . I would like to for each descriptor to be a column.

                    Here is my code:

                    rename YEAR* _*
                    reshape long _, i(CountryCode Descriptor Units CountryocdevariableNotes) j(year)
                    replace Descriptor = strtoname(Descriptor)
                    levelsof Descriptor if strlen(Descriptor) > 32
                    reshape wide _, i(CountryCode Units CountryocdevariableNotesyear ) j(Descriptor) string
                    rename _* *

                    Code:
                    * Example generated by -dataex-. For more info, type help dataex
                    clear
                    input str3 CountryCode str24 Descriptor str19 Units str23 CountryocdevariableNotes double(YEAR1980 YEAR1981 YEAR1982 YEAR1983 YEAR1984)
                    "ARG" "Gov sepnding/expenditure" "Millions of dollars" "relates to category A "   527.7139999999999         1.75215677           2.624572         5.58955748          5.2625026
                    "ARG" "assate valuation"         "Nominal value"       "relates to categorycD "          239363.617 2808.3978663000003       2246.9102336     2891.193679205 2388.7589206000002
                    "ARG" "Total investment"         "Percent of GDP"      "relates to category A "  1598.3310000000001        20.43066038         15.9297376 23.611741400000003         20.7388336
                    "ARG" "Import (US dollar)"       "US currency"         "relates to: category C "           1044.887 14.553347120000002          13.574695         19.4924977         17.1610862
                    "BEL" "Gov sepnding/expenditure" "Millions of dollars" "relates to category A "             359.141        3.864008265 4.6817964000000005  6.112839395000001          5.1886374
                    "BEL" "assate valuation"         "Nominal value"       "relates to categorycD "  154070.65899999999 1845.3492386550001         1514.04015 1959.5656176200002 1620.2308046000003
                    "BEL" "Total investment"         "Percent of GDP"      "relates to category A "            1598.746        19.14758495 16.380786800000003       23.551628035 20.353005800000002
                    "BEL" "Import (US dollar)"       "US currency"         "relates to: category C "           1004.134       11.690571295 11.598408000000001        18.61937571         17.0652186
                    "CAN" "Gov sepnding/expenditure" "Millions of dollars" "relates to category A "             729.902        8.714466995  5.636543400000001        6.053711495 2.9475358000000003
                    "CAN" "assate valuation"         "Nominal value"       "relates to categorycD "  348564.14400000003      4390.34315057 3656.8428848000003       4744.4029867 3825.5808620000007
                    "CAN" "Total investment"         "Percent of GDP"      "relates to category A "  1949.9189999999999       23.143645525 16.677819200000002 18.404544339999998          15.224875
                    "CAN" "Import (US dollar)"       "US currency"         "relates to: category C "           1271.975       22.195628195         16.0727532       16.838640455          11.775213
                    "GNB" "Gov sepnding/expenditure" "Millions of dollars" "relates to category A "              80.095  6.044842310000001 4.1702406000000005        4.719391885 2.9553938000000004
                    "GNB" "assate valuation"         "Nominal value"       "relates to categorycD "          170469.633      2101.91012408       1726.9895926      2220.66851123 1797.7933158000003
                    "GNB" "Total investment"         "Percent of GDP"      "relates to category A "  1671.6200000000001       14.757338375         14.2473398       17.857611265 15.061428600000003
                    "GNB" "Import (US dollar)"       "US currency"         "relates to: category C "           1712.622 16.553841069999997 15.314456200000002        18.11678856 12.323701400000001
                    "COL" "Gov sepnding/expenditure" "Millions of dollars" "relates to category A "             588.055         6.08228998 4.5985016000000005 5.2278918249999995 3.7294068000000005
                    "COL" "assate valuation"         "Nominal value"       "relates to categorycD "          166193.888       2030.1170279 1659.7258984000002     2122.499444325        1717.393403
                    "COL" "Total investment"         "Percent of GDP"      "relates to category A "             1673.28       20.459238865 18.830125400000004        21.27618935         16.0758964
                    "COL" "Import (US dollar)"       "US currency"         "relates to: category C "           1282.516       15.212623205 13.139361800000001       14.097076825         12.2262622
                    "DAN" "Gov sepnding/expenditure" "Millions of dollars" "relates to category A "             551.452        4.329147745          4.5112778  4.928310465000001          5.5061006
                    "DAN" "assate valuation"         "Nominal value"       "relates to categorycD "           93524.151      1115.07138587  904.5933150000001 1146.3185100900002  941.4866250000001
                    "DAN" "Total investment"         "Percent of GDP"      "relates to category A "            2590.679 31.897531120000004 22.502954600000002       25.839877765          22.807845
                    "DAN" "Import (US dollar)"       "US currency"         "relates to: category C " 1578.2450000000001        16.63267827          13.421464       14.510972125 12.839972000000001
                    "GER" "Gov sepnding/expenditure" "Millions of dollars" "relates to category A "             516.592  6.274455655000001          4.9945448 7.2993392550000005          4.8790322
                    "GER" "assate valuation"         "Nominal value"       "relates to categorycD "  238070.22799999997     2923.337576575       2410.5719428      3157.33722629       2600.5940988
                    "GER" "Total investment"         "Percent of GDP"      "relates to category A "            2148.123       25.457517345         19.9294596        29.38262444 25.654798400000004
                    "GER" "Import (US dollar)"       "US currency"         "relates to: category C " 1568.6999999999998        19.82558487         16.6385292 22.212381099999998 18.733472000000003
                    "RUS" "Gov sepnding/expenditure" "Millions of dollars" "relates to category A "              491.36 5.6565691000000005            4.40048         4.28480182          3.9030686
                    "RUS" "assate valuation"         "Nominal value"       "relates to categorycD "          151406.774      1853.83310684       1522.6784494     1944.023849105 1587.9297098000002
                    "RUS" "Total investment"         "Percent of GDP"      "relates to category A "            1711.211       22.885453695         16.8263354       18.050762405         14.2261232
                    "RUS" "Import (US dollar)"       "US currency"         "relates to: category C "           1147.475 15.376210395000001         11.1379292       16.560739325 12.183829000000001
                    end
                    Can someone assist? Thank you

                    Comment


                    • #11
                      In
                      Code:
                      reshape wide _, i(CountryCode Units CountryocdevariableNotesyear ) j(Descriptor) string
                      you need to insert a blank space between CountryocdevariableNotes and year. Without that, Stata thinks you want one variable named CountryocdevariableNotesyear, but there is no such variable.

                      Comment


                      • #12
                        Thank you

                        Comment


                        • #13
                          Hello,

                          I have another problem.

                          My data is the following:

                          Code:
                          * Example generated by -dataex-. For more info, type help dataex
                          clear
                          input str3 ISO str54 SubjectDescriptor double(YEAR1990 YEAR1991 YEAR1992)
                          "ARG" "Volume of transferred commodities"                                   12.231          16.5317945           18.968404
                          "ARG" "Volume of transferred assets"                            18.485100000000003           18.083376           20.748672
                          "ARG" "Genral government spensding"                                         9.5535             8.51819             9.77368
                          "ARG" "Demography in zone"                                                 11.3166           8.4266535            9.668652
                          "ARG" "Population"                                                         12.8025  11.671282000000001  13.391504000000001
                          "ARG" "General government net lending/borrowing"                           -1.4859 -3.2446284999999997 -3.7228519999999996
                          "ARG" "General government gross debt"                                      20.0538          27.1757495  31.181164000000003
                          "ARG" "Current account balance"                                            -6.0012          -2.2672305           -2.601396
                          "ARG" "Gross national product per population, currentt prices"  2.9614533825885543  .25407223349791586  .29151976031221544
                          "BEL" "Volume of transferred commodities"                        9.016200000000001           8.8306245           10.132164
                          "BEL" "Volume of transferred assets"                                        10.908           9.4509545           10.843924
                          "BEL" "Genral government spensding"                                         16.353          14.5815375             16.7307
                          "BEL" "Demography in zone"                                                  17.271           14.046692           16.117024
                          "BEL" "Population"                                                         18.8352           16.381251           18.795672
                          "BEL" "General government net lending/borrowing"               -1.5633000000000001          -2.3338025            -2.67778
                          "BEL" "General government gross debt"                                      22.3902  25.314759499999997           29.045884
                          "BEL" "Current account balance"                                            -6.4485 -4.6040589999999995           -5.282648
                          "BEL" "Gross national product per population, currentt prices"    1.13759690496708  2.1889314961691566  2.5115565613679154
                          "CAN" "Volume of transferred commodities"                                  11.2968          -5.5837265           -6.406708
                          "CAN" "Volume of transferred assets"                                       11.2968          -5.5837265           -6.406708
                          "CAN" "Genral government spensding"                                         20.799          18.4033755           21.115836
                          "CAN" "Demography in zone"                                                 12.2805           11.046413           12.674536
                          "CAN" "Population"                                                         13.6926          13.3000265           15.260308
                          "CAN" "General government net lending/borrowing"                           -1.4112          -2.2536135           -2.585772
                          "CAN" "General government gross debt"                                       24.039           23.540767  27.010423999999997
                          "CAN" "Current account balance"                                  .9422999999999999            -.649077            -.744744
                          "CAN" "Gross national product per population, currentt prices"   5.433401230394225   3.371630260966608  3.8685724606992937
                          "FRA" "Volume of transferred commodities"                       7.2242999999999995             .609739             .699608
                          "FRA" "Volume of transferred assets"                                        6.1173 -1.7936614999999998           -2.058028
                          "FRA" "Genral government spensding"                                         1.4337            1.258816  1.4443519999999999
                          "FRA" "Demography in zone"                                                 18.4707          11.4798875             13.1719
                          "FRA" "Population"                                                         20.6631  15.520353999999998           17.807888
                          "FRA" "General government net lending/borrowing"                           -2.1924          -4.0404665           -4.635988
                          "FRA" "General government gross debt"                                      54.9522  45.271229500000004           51.943724
                          "FRA" "Current account balance"                                              .4446            1.027327            1.178744
                          "FRA" "Gross national product per population, currentt prices" -1.0879909997748838  2.2995500622084544  2.6384791196258273
                          "GER" "Volume of transferred commodities"                      -1.6542000000000001           -2.951863           -3.386936
                          "GER" "Volume of transferred assets"                           -2.7036000000000002           -3.178813           -3.647336
                          "GER" "Genral government spensding"                                        15.7968            14.14655             16.2316
                          "GER" "Demography in zone"                                      15.417900000000001          13.8537845           15.895684
                          "GER" "Population"                                                         18.0162           16.838177           19.319944
                          "GER" "General government net lending/borrowing"               -2.5974000000000004          -2.9843925            -3.42426
                          "GER" "General government gross debt"                                      24.2163           27.247617           31.263624
                          "GER" "Current account balance"                                -4.2372000000000005           -5.478573           -6.286056
                          "GER" "Gross national product per population, currentt prices"  3.3683750279187574  1.9127710623198528  2.1946930364753894
                          "ITA" "Volume of transferred commodities"                                    .4131           -5.564814           -6.385008
                          "ITA" "Volume of transferred assets"                                       -6.6564  -4.340040500000001           -4.979716
                          "ITA" "Genral government spensding"                                         17.316  15.726878500000002  18.044852000000002
                          "ITA" "Demography in zone"                                      15.762599999999999           11.270337           12.931464
                          "ITA" "Population"                                                         21.2751  14.647352999999999           16.806216
                          "ITA" "General government net lending/borrowing"                           -5.5125 -3.3770160000000002 -3.8747520000000004
                          "ITA" "General government gross debt"                           19.877399999999998  24.833625499999997  28.493835999999998
                          "ITA" "Current account balance"                                           -10.8612  -8.636960499999999           -9.909956
                          "ITA" "Gross national product per population, currentt prices"  2.3085606589478602  1.3141700893461277  1.5078646894282073
                          "LIT" "Volume of transferred commodities"                       3.7134000000000005  3.5358810000000003            4.057032
                          "LIT" "Volume of transferred assets"                                        6.1533  4.6683615000000005            5.356428
                          "LIT" "Genral government spensding"                                        12.7575  11.342960999999999           13.014792
                          "LIT" "Demography in zone"                                                 17.2836  15.652741499999998           17.959788
                          "LIT" "Population"                                              20.790000000000003          18.1280095  20.799884000000002
                          "LIT" "General government net lending/borrowing"                           -3.5064           -2.475268           -2.840096
                          "LIT" "General government gross debt"                           38.129400000000004           35.951906           41.250832
                          "LIT" "Current account balance"                                            -6.2829 -3.1682219999999997 -3.6351839999999997
                          "LIT" "Gross national product per population, currentt prices"  2.9557217875748862   2.581044607645042    2.96146294704018
                          "RUS" "Volume of transferred commodities"                                   -6.849            4.268173  4.8972560000000005
                          "RUS" "Volume of transferred assets"                            -9.266399999999999             3.81276             4.37472
                          "RUS" "Genral government spensding"                              6.559200000000001           5.7955465            6.649748
                          "RUS" "Demography in zone"                                                 13.7835           12.090383           13.872376
                          "RUS" "Population"                                                         20.4156  17.439594500000002  20.010004000000002
                          "RUS" "General government net lending/borrowing"                           -6.6321          -5.3492115  -6.137627999999999
                          "RUS" "General government gross debt"                                      41.8158  45.500448999999996           52.206728
                          "RUS" "Current account balance"                                            -6.1128          -5.4763035           -6.283452
                          "RUS" "Gross national product per population, currentt prices"   2.945530033212202  2.2747871283607237  2.6100663944707314
                          end

                          I used the below code:


                          rename YEAR* _*
                          reshape long _, i(ISO SubjectDescriptor) j(year)

                          replace SubjectDescriptor = strtoname(SubjectDescriptor)
                          levelsof SubjectDescriptor if strlen(SubjectDescriptor) > 32

                          reshape wide _, i(ISO year) j(SubjectDescriptor) string
                          rename _* *
                          And I get the following error message:


                          . reshape wide _, i(ISO year) j(SubjectDescriptor) string
                          (j = Current_account_balance Demography_in_zone General_government_gross_debt General_gover
                          > nment_net_lending_b Genral_government_spensding Gross_national_product_per_popul Populati
                          > on Volume_of_transferred_assets Volume_of_transferred_commoditie)
                          _General_government_net_lending_b invalid variable name


                          Can you assist?


                          Thank you

                          Comment


                          • #14
                            It's too long. The maximum allowable length is 32 characters. You will need to abbreviate it in some way in the variable SubjectDescriptor before you can do the -reshape-.

                            Comment


                            • #15
                              Perfect. thank you.

                              Comment

                              Working...
                              X