Announcement

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

  • Reshaping multiple variables

    Dear reader,

    I have a dataset with many variables for different key statistics in a company that starts in 2010 and all end in 2018. For the dataexample I only used data from 2015-2018 as there is a limit in the amount of variables that can be used in the example. The format now is wide as can be seen in the dataex example; I'd like to reshape them to a long format in an easy way without having to do this manually for each variableset (9 sets of 9 years). Is there an easy way?

    Kind regards,





    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input str10 DealNumber float(yearacq yearexit) int(Numberofemployees2018 Numberofemployees2017 Numberofemployees2016 Numberofemployees2015) double(OperatingrevenueTurnoverth V W X Operatingrevenueperemployeet AE AF AG)
    "1601223111" 2010 2018 .    .    .    . . 82899.65217340708  70054.8084720111 75388.16046497309 .                 .                 .                 .
    "1601448192" 2011 2016 .    .    .    . .                 .                 .  223834.515466833 .                 .                 .                 .
    "1601492008" 2011 2017 .  181  175  156 . 49112.43579444362 39813.26722258594 47462.17873775931 . 271.3394241693869 227.5043835973997 304.2447358515662
    "1601392471" 2011 2016 .    .    .   82 .                 .                 . 83193.96917511761 .                 .                 . 1014.560600147968
    "1909174393" 2013 2017 .    .    .    . . 52022.01761698723 41865.67221593857 40790.32053458691 .                 .                 .                 .
    "1909481217" 2015 2017 . 2353 2363 1741 . 103327.7943396492 81523.50233502245 50213.00433506954 . 43.91321499538088 34.50000080571127 28.84147292429543
    "1601301339" 2013 2018 .  492  413  351 . 116141.9960702643 87919.36525676394 79297.69826876068 .  236.060967875999  212.879818559123 225.9193682250333
    "1601360939" 2011 2017 .    .  330  343 .                 .  91731.9600906372  65879.4105796814 .                 . 277.9756367341218 192.0682523395449
    "1601366984" 2011 2017 .    .  830  825 .                 . 138598.6780137248  134310.638005784 .                 . 166.9863591923805 162.8007733073534
    "1601366987" 2011 2017 .    .  830  825 .                 . 138598.6780137248  134310.638005784 .                 . 166.9863591923805 162.8007733073534
    end

  • #2
    I think you need to do some renaming first. The variable names show signs that Stata was struggling with repeated metadata in the first row(s) of a spreadsheet.

    Comment


    • #3
      Thanks, if I rename the variables as such;

      Code:
      * Example generated by -dataex-. To install: ssc install dataex
      clear
      input str10 DealNumber float(yearacq yearexit) int(Numberofemployees2018 Numberofemployees2017 Numberofemployees2016 Numberofemployees2015) double(OR18 OR17 OR16 OR15 ORpe18 ORpe17 ORpe16 ORpe15)
      "1601223111" 2010 2018   .    .    .    .                . 82899.65217340708  70054.8084720111 75388.16046497309                 .                 .                 .                 .
      "1601448192" 2011 2016   .    .    .    .                .                 .                 .  223834.515466833                 .                 .                 .                 .
      "1909476733" 2011 2016 185  207  223  138 60515.1475521946 65284.98149850369 59876.78996825481 42518.15861338913 327.1089060656357 315.3863840176642 268.5057849495444 308.1025984045083
      "1601492008" 2011 2017   .  181  175  156                . 49112.43579444362 39813.26722258594 47462.17873775931                 . 271.3394241693869 227.5043835973997 304.2447358515662
      "1601392471" 2011 2016   .    .    .   82                .                 .                 . 83193.96917511761                 .                 .                 . 1014.560600147968
      "1909174393" 2013 2017   .    .    .    .                . 52022.01761698723 41865.67221593857 40790.32053458691                 .                 .                 .                 .
      "1909481217" 2015 2017   . 2353 2363 1741                . 103327.7943396492 81523.50233502245 50213.00433506954                 . 43.91321499538088 34.50000080571127 28.84147292429543
      "1601301339" 2010 2018   .  492  413  351                . 116141.9960702643 87919.36525676394 79297.69826876068                 .  236.060967875999  212.879818559123 225.9193682250333
      "1601313245" 2012 2014   .    .    .    .                .                 .                 .                 .                 .                 .                 .                 .
      "1601360939" 2011 2017   .    .  330  343                .                 .  91731.9600906372  65879.4105796814                 .                 . 277.9756367341218 192.0682523395449
      end
      Is there then an easy way to reshape the variables?

      Comment


      • #4
        That's progress. You need to keep going further in the same direction:


        Code:
        rename (OR??) (OR20??)
        rename (ORpe??) (ORpe20??)
        reshape long Numberofemployees OR ORpe , i(DealNumber) j(year)
        Last edited by Nick Cox; 11 Jun 2019, 09:42.

        Comment

        Working...
        X