Announcement

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

  • How to insert next period Net income ratio for each year

    Hi everyone,

    I have following data. I want to create variable ROA t+1 which is next period ROA. I need to put next year ROA for ROAt+1 at each year. for example, I need to put ROA for 1964 in ROAt+1 at year 1963 as following data.

    ID fyear ROA ROA t+1
    001000 1962 0 0
    001000 1963 0 3.672316
    001000 1964 3.672316 -8.528138
    001000 1965 -8.528138 6.748971
    001000 1966 6.748971 -3.664495 and for all other years
    001000 1967 -3.664495
    001000 1968 8.737305
    001000 1969 6.150738
    001000 1970 1.668161
    001000 1971 -7.903171
    001000 1972 11.17697
    001000 1973 8.557255
    001000 1974 8.737031
    001000 1975 9.554486
    001000 1976 8.899601
    001000 1977 4.37933
    001001 1978 0
    001001 1979 0
    001001 1980 11.176
    001001 1981 8.737
    001001 1982 6.748
    001001 1983 8.06108
    001001 1984 6.995758
    001001 1985 6.522345

    Could you please help me with the Stata command?

  • #2
    A number of commands accept time-series operators, so there is no need of generating a lead variable.

    Code:
    help tsvarlist
    But you want:

    Code:
    xtset ID fyear
    gen wanted= F.ROA

    Comment


    • #3
      Thanks Andrew.
      it is my data:

      * Example generated by -dataex-. For more info, type help dataex
      clear
      input str6 ID double fyear float ROA
      "001000" 1961 .
      "001000" 1962 .
      "001000" 1963 .
      "001000" 1964 3.672316
      "001000" 1965 -8.528138
      "001000" 1966 6.748971
      "001000" 1967 -3.664495
      "001000" 1968 7.818305
      "001000" 1969 6.150738
      "001000" 1970 1.6681614
      "001000" 1971 -7.903171
      "001000" 1972 11.176973
      "001000" 1973 8.557255
      "001000" 1974 8.737031
      "001000" 1975 9.554486
      "001000" 1976 8.899601
      "001000" 1977 4.37933
      "001001" 1978 .
      "001001" 1979 .
      "001001" 1980 .
      "001001" 1981 .
      "001001" 1982 .
      "001001" 1983 8.06108
      "001001" 1984 6.995758
      "001001" 1985 6.522345
      "001002" 1960 .
      "001002" 1961 .
      "001002" 1962 .
      "001002" 1963 .
      "001002" 1964 .
      "001002" 1965 .
      "001002" 1966 .
      "001002" 1967 .
      "001002" 1968 1.6700586
      "001002" 1969 .6107681
      "001002" 1970 3.549045
      "001002" 1971 6.566031
      "001002" 1972 5.728502
      "001003" 1980 .
      "001003" 1981 .
      "001003" 1982 16.495028
      "001003" 1983 12.31094
      "001003" 1984 4.696032
      "001003" 1985 1.6869192
      "001003" 1986 5.43672
      "001003" 1987 1.1594564
      "001003" 1988 -48.14496
      "001003" 1989 -2.1861708
      "001004" 1965 13.894403
      "001004" 1966 7.056146
      "001004" 1967 .307377


      I run your command but it gave me this error:

      xtset ID fyear
      string variables not allowed in varlist;
      ID is a string variable
      r(109);

      gen wanted= F.ROA
      time variable not set
      r(111);

      I want to create a new variable named ROAt1 which its value for each year is value of ROA for next year. for example ROAt1 in 1963 = 3.672316

      Comment


      • #4
        You need a numerical identifier. See

        Code:
        help encode
        Code:
        encode ID, gen(id)
        xtset id fyear
        gen double wanted= F.ROA

        Comment


        • #5
          Thanks a bunch, Andrew. But, It gave me this error:

          encode ID, gen(id)

          . xtset id fyear
          repeated time values within panel
          r(451);

          . gen double wanted= F.ROA
          time variable not set
          Last edited by Fimi Karimi; 02 Jul 2023, 00:05.

          Comment


          • #6
            Why do you have duplicates of firm and year?

            Code:
            bys id fyear: gen tag= _N>1
            list if tag, sepby(id fyear)
            If the duplicates are perfect duplicates:

            Code:
            duplicates drop id fyear, force
            xtset id fyear
            gen double wanted= F.ROA

            Comment


            • #7
              It's a panel data, thats why the firms repeat in each year. If I drop duplicates Id it makes a mess.

              Comment


              • #8
                Again, if you have a panel dataset where the variable "ID" is the panel identifier, you have at most one observation (of each ID) in a year. If this is not the case, then you do not have panel data and therefore cannot use time-series operators. Look at the structure of the Grunfeld dataset below. This is company-level panel data. As you can see, you have exactly one observation of each firm in a year.

                Code:
                webuse grunfeld, clear
                list if company<=4, sepby(company)
                Res.:

                Code:
                . 
                . list if company<=4, sepby(company)
                
                     +--------------------------------------------------+
                     | company   year   invest   mvalue   kstock   time |
                     |--------------------------------------------------|
                  1. |       1   1935    317.6   3078.5      2.8      1 |
                  2. |       1   1936    391.8   4661.7     52.6      2 |
                  3. |       1   1937    410.6   5387.1    156.9      3 |
                  4. |       1   1938    257.7   2792.2    209.2      4 |
                  5. |       1   1939    330.8   4313.2    203.4      5 |
                  6. |       1   1940    461.2   4643.9    207.2      6 |
                  7. |       1   1941      512   4551.2    255.2      7 |
                  8. |       1   1942      448   3244.1    303.7      8 |
                  9. |       1   1943    499.6   4053.7    264.1      9 |
                 10. |       1   1944    547.5   4379.3    201.6     10 |
                 11. |       1   1945    561.2   4840.9      265     11 |
                 12. |       1   1946    688.1   4900.9    402.2     12 |
                 13. |       1   1947    568.9   3526.5    761.5     13 |
                 14. |       1   1948    529.2   3254.7    922.4     14 |
                 15. |       1   1949    555.1   3700.2   1020.1     15 |
                 16. |       1   1950    642.9   3755.6     1099     16 |
                 17. |       1   1951    755.9     4833   1207.7     17 |
                 18. |       1   1952    891.2   4924.9   1430.5     18 |
                 19. |       1   1953   1304.4   6241.7   1777.3     19 |
                 20. |       1   1954   1486.7   5593.6   2226.3     20 |
                     |--------------------------------------------------|
                 21. |       2   1935    209.9   1362.4     53.8      1 |
                 22. |       2   1936    355.3   1807.1     50.5      2 |
                 23. |       2   1937    469.9   2676.3    118.1      3 |
                 24. |       2   1938    262.3   1801.9    260.2      4 |
                 25. |       2   1939    230.4   1957.3    312.7      5 |
                 26. |       2   1940    361.6   2202.9    254.2      6 |
                 27. |       2   1941    472.8   2380.5    261.4      7 |
                 28. |       2   1942    445.6   2168.6    298.7      8 |
                 29. |       2   1943    361.6   1985.1    301.8      9 |
                 30. |       2   1944    288.2   1813.9    279.1     10 |
                 31. |       2   1945    258.7   1850.2    213.8     11 |
                 32. |       2   1946    420.3   2067.7    132.6     12 |
                 33. |       2   1947    420.5   1796.7    264.8     13 |
                 34. |       2   1948    494.5   1625.8    306.9     14 |
                 35. |       2   1949    405.1     1667    351.1     15 |
                 36. |       2   1950    418.8   1677.4    357.8     16 |
                 37. |       2   1951    588.2   2289.5    342.1     17 |
                 38. |       2   1952    645.5   2159.4    444.2     18 |
                 39. |       2   1953      641   2031.3    623.6     19 |
                 40. |       2   1954    459.3   2115.5    669.7     20 |
                     |--------------------------------------------------|
                 41. |       3   1935     33.1   1170.6     97.8      1 |
                 42. |       3   1936       45   2015.8    104.4      2 |
                 43. |       3   1937     77.2   2803.3      118      3 |
                 44. |       3   1938     44.6   2039.7    156.2      4 |
                 45. |       3   1939     48.1   2256.2    172.6      5 |
                 46. |       3   1940     74.4   2132.2    186.6      6 |
                 47. |       3   1941      113   1834.1    220.9      7 |
                 48. |       3   1942     91.9     1588    287.8      8 |
                 49. |       3   1943     61.3   1749.4    319.9      9 |
                 50. |       3   1944     56.8   1687.2    321.3     10 |
                 51. |       3   1945     93.6   2007.7    319.6     11 |
                 52. |       3   1946    159.9   2208.3      346     12 |
                 53. |       3   1947    147.2   1656.7    456.4     13 |
                 54. |       3   1948    146.3   1604.4    543.4     14 |
                 55. |       3   1949     98.3   1431.8    618.3     15 |
                 56. |       3   1950     93.5   1610.5    647.4     16 |
                 57. |       3   1951    135.2   1819.4    671.3     17 |
                 58. |       3   1952    157.3   2079.7    726.1     18 |
                 59. |       3   1953    179.5   2371.6    800.3     19 |
                 60. |       3   1954    189.6   2759.9    888.9     20 |
                     |--------------------------------------------------|
                 61. |       4   1935    40.29    417.5     10.5      1 |
                 62. |       4   1936    72.76    837.8     10.2      2 |
                 63. |       4   1937    66.26    883.9     34.7      3 |
                 64. |       4   1938     51.6    437.9     51.8      4 |
                 65. |       4   1939    52.41    679.7     64.3      5 |
                 66. |       4   1940    69.41    727.8     67.1      6 |
                 67. |       4   1941    68.35    643.6     75.2      7 |
                 68. |       4   1942     46.8    410.9     71.4      8 |
                 69. |       4   1943     47.4    588.4     67.1      9 |
                 70. |       4   1944    59.57    698.4     60.5     10 |
                 71. |       4   1945    88.78    846.4     54.6     11 |
                 72. |       4   1946    74.12    893.8     84.8     12 |
                 73. |       4   1947    62.68      579     96.8     13 |
                 74. |       4   1948    89.36    694.6    110.2     14 |
                 75. |       4   1949    78.98    590.3    147.4     15 |
                 76. |       4   1950   100.66    693.5    163.2     16 |
                 77. |       4   1951   160.62      809    203.5     17 |
                 78. |       4   1952      145      727    290.6     18 |
                 79. |       4   1953   174.93   1001.5    346.1     19 |
                 80. |       4   1954   172.49    703.2    414.9     20 |
                     +--------------------------------------------------+
                
                .

                Comment


                • #9
                  Thank you Andrew for your help. It works.

                  Comment


                  • #10
                    Hello Andrew,

                    Sorry for bothering you again. Could you please help me with these 2 questions?
                    1-In #6 you gave me a command for calculating the next period value of a variable. Could I ask you for your help on how to calculate the previous period value of that variable? I checked help xtset but I couldn't figure it out.
                    2- I need to include age of individual in my panel data, (2023-YearofBirth) and (YearofDeath-YearofBirth) for those who are not alive. The age also needs to be adjusted at each year. I mean a person who is 37 years old in 2005 must be 36 in 2004 and 35 in 2003 and so on. Could you please tell me how I can do that?
                    * Example generated by -dataex-. For more info, type help dataex
                    clear
                    input int Year long(ClubID IndividualID) str1 Gender int(YearofBirth YearofDeath) str17 Nationality
                    1934 3 59796 "M" 1944 2024 "American"
                    1934 3 340769 "M" 1970 2024 "American"
                    1934 3 340791 "M" 1940 2014 "American"
                    1934 3 340825 "M" 1961 2024 "American"
                    1934 3 340842 "M" 1961 2024 "American"
                    1934 3 341118 "M" 1969 2024 "American"
                    1934 3 341132 "M" 1959 2024 "American"
                    1934 3 341153 "M" 1970 2024 "American"
                    1934 3 341168 "M" 1959 2024 "American"
                    1934 3 341583 "M" 1960 2024 "American"
                    1934 3 505279 "M" 1968 2024 ""
                    1934 3 547192 "M" 1942 2024 ""
                    1934 6 33553 "M" 1955 2024 "American"
                    1934 6 36503 "M" 1956 2024 "American"
                    1934 6 37576 "F" 1951 2024 "American"
                    1934 6 37576 "F" 1951 2024 "American"
                    1934 6 37576 "F" 1951 2024 "American"
                    1934 6 86917 "M" 1951 2024 "American"
                    1934 6 87882 "M" 1944 2024 ""
                    1934 6 141620 "M" 1961 2024 "American"
                    1934 6 327069 "M" 1961 2024 "American"
                    1934 6 340891 "M" 1951 2024 ""
                    1934 6 341596 "M" 1952 2024 "American"
                    1934 6 341598 "F" 1953 2024 "American"
                    1934 6 341863 "M" 1964 2024 "American"
                    1934 6 341863 "M" 1964 2024 "American"
                    1934 6 370490 "M" 1944 2024 "American"
                    1934 6 457013 "F" 1962 2024 "American"
                    1934 6 533773 "F" 1954 2024 ""
                    1934 6 747738 "F" 1969 2024 ""
                    1934 6 879271 "M" 1954 2024 ""
                    1934 6 1057384 "M" 1950 2024 ""
                    1934 6 1112878 "M" 1947 2024 ""
                    1934 6 1299622 "M" 1959 2024 ""
                    1934 6 1366174 "M" 1957 2024 ""
                    1934 6 1373247 "M" 1963 2024 ""
                    1934 6 1391443 "F" 1963 2024 ""
                    1934 6 2408676 "F" 1969 2024 ""
                    1934 12 8066 "M" 1935 2024 "Canadian"
                    1934 12 32412 "M" 1945 2024 ""
                    1934 12 36354 "M" 1948 2024 "American"
                    1934 12 40911 "M" 1957 2024 ""
                    1934 12 140120 "M" 1952 2024 "American"
                    1934 12 140488 "F" 1946 2024 "American"
                    1934 12 201375 "M" 1919 2011 "American"
                    1934 12 310234 "M" 1939 2024 "American"
                    1934 12 320417 "M" 1947 2024 "American"
                    1934 12 324072 "M" 1939 2017 ""
                    1934 12 341468 "M" 1953 2024 "American"
                    1934 12 341979 "M" 1946 2024 "American"
                    1934 12 371952 "M" 1965 2024 "American"
                    1934 12 371960 "M" 1964 2024 ""
                    1934 12 374153 "M" 1946 2024 ""
                    1934 12 374182 "M" 1955 2024 ""
                    1934 12 374216 "M" 1960 2024 "American"
                    1934 12 374225 "M" 1941 2024 ""
                    1934 12 374241 "M" 1949 2024 ""
                    1934 12 374273 "M" 1944 2024 ""
                    1934 12 374276 "M" 1949 2024 ""
                    1934 12 374408 "M" 1945 2024 ""
                    1934 12 374449 "M" 1939 2024 ""
                    1934 12 374887 "M" 1945 2024 ""
                    1934 12 374892 "F" 1959 2024 ""
                    1934 12 493920 "M" 1965 2024 ""
                    1934 12 551393 "M" 1948 2024 ""
                    1934 12 552193 "M" 1962 2024 ""
                    1934 12 555070 "M" 1953 2024 ""
                    1934 12 593295 "M" 1952 2024 ""
                    1934 12 1264202 "M" 1960 2024 "American"
                    1934 22 746970 "M" 1937 2024 ""
                    1934 22 746973 "M" 1950 2011 ""
                    1934 22 746984 "M" 1941 2024 ""
                    1934 22 1146025 "F" 1974 2024 ""
                    1934 26 338835 "M" 1941 2024 "American"
                    1934 26 373892 "M" 1956 2024 "American"
                    1934 26 373894 "M" 1958 2024 "American"
                    1934 26 373897 "M" 1929 2024 "American"
                    1934 26 373899 "M" 1954 2024 "American"
                    1934 26 481237 "M" 1955 2009 ""
                    1934 28 14242 "M" 1938 2024 "American"
                    1934 28 102642 "M" 1949 2024 "American"
                    1934 28 220968 "M" 1952 2024 "American"
                    1934 28 335966 "M" 1930 2019 "American"
                    1934 28 335970 "M" 1939 2024 "American"
                    1934 28 335975 "M" 1952 2024 "American"
                    1934 28 335980 "M" 1935 2024 "American"
                    1934 28 335983 "M" 1948 2024 "American"
                    1934 28 335986 "M" 1933 2024 ""
                    1934 28 335987 "F" 1947 2024 "American"
                    1934 28 335989 "M" 1939 2024 "American"
                    1934 28 335990 "M" 1939 2024 "American"
                    1934 28 335993 "M" 1945 2024 "American"
                    1934 28 335994 "M" 1930 2014 "American"
                    1934 28 335996 "M" 1935 2024 "American"
                    1934 28 335999 "M" 1940 2024 ""
                    1934 28 336002 "F" 1951 2024 "American"
                    1934 28 336004 "M" 1936 2024 "American"
                    1934 28 336006 "M" 1943 2015 "American"
                    1934 28 336006 "M" 1943 2015 "American"
                    1934 28 336025 "F" 1958 2024 "American"
                    end
                    [/CODE]
                    ------------------ copy up to and including the previous line ------------------
                    Listed 100 out of 19446063 observations

                    Thanks in advance for your help.

                    Comment


                    • #11
                      Originally posted by Fimi Karimi View Post
                      1-In #6 you gave me a command for calculating the next period value of a variable. Could I ask you for your help on how to calculate the previous period value of that variable? I checked help xtset but I couldn't figure it out.
                      The information is available at

                      Code:
                      help tsvarlist
                      The lead operator is F. while the lag operator is L., so you want

                      Code:
                      gen double wanted= L.ROA
                      for the lagged return on assets.

                      2- I need to include age of individual in my panel data, (2023-YearofBirth) and (YearofDeath-YearofBirth) for those who are not alive. The age also needs to be adjusted at each year. I mean a person who is 37 years old in 2005 must be 36 in 2004 and 35 in 2003 and so on. Could you please tell me how I can do that?
                      Assuming "Year" refers to the observation year in the panel, you just create an age variable based on this.

                      Code:
                      gen age = cond(Year<=YearofDeath & Year>=YearofBirth, Year-YearofBirth, .)
                      This will assign missing values for age for those who are dead in the years after their death or those not yet born at the observed year, otherwise the age at the observed year.




                      Comment


                      • #12
                        Thank you so much for your help.

                        Comment

                        Working...
                        X