Announcement

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

  • reshaping quarterly data

    I'm using Stata 14 on Windows 10 OS.

    I have quarterly data on a wide form. Here is a pick of my data

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input str13 Nome str10(CtaRecCPDez2017 CtaRecCPSet2017 CtaRecCPJun2017 ClieCPDez2017 ClieCPSet2017 ClieCPJun2017 FornecDez2017) str9 FornecSet2017
    "Nordon Met"   "500"  "496"  "493"  "0"    "0"    "0"    "382"   "125"
    "Lojas Hering" "567"  "534"  "489"  "567"  "534"  "489"  "77"    "87"  
    "Lojas Hering" "567"  "534"  "489"  "567"  "534"  "489"  "77"    "87"  
    "Ideiasnet"    "820"  "656"  "573"  "0"    "186"  "287"  "741"   "789"
    "IGB S/A"      "877"  "827"  "780"  "0"    "0"    "0"    "17577" "4282"
    "Aliperti"     "1074" "4074" "9114" "1074" "4074" "9114" "2404"  "4637"
    "Aliperti"     "1074" "4074" "9114" "1074" "4074" "9114" "2404"  "4637"
    "Hercules"     "1098" "1102" "895"  "916"  "998"  "783"  "42"    "24"  
    "Hercules"     "1098" "1102" "895"  "916"  "998"  "783"  "42"    "24"  
    "J B Duarte"   "1145" "1145" "1144" "0"    "0"    "0"    "734"   "1088"
    "J B Duarte"   "1145" "1145" "1144" "0"    "0"    "0"    "734"   "1088"
    end

    I want to reshaping to long extracting the dates from variables names. Is that a way to do it quickly for all my variables?

  • #2
    Hi Felipe,

    Your data is long already (you have more than one observation for some identifiers - see that Nome appears twice for some of your data). If you want to convert it to wide (a single observation for each identifier, with multiple columns specifying variables), try the code below.

    Code:
    clear
    input str13 Nome str10(CtaRecCPDez2017 CtaRecCPSet2017 CtaRecCPJun2017 ClieCPDez2017 ClieCPSet2017 ClieCPJun2017 FornecDez2017) str9 FornecSet2017
    "Nordon Met"   "500"  "496"  "493"  "0"    "0"    "0"    "382"   "125"
    "Lojas Hering" "567"  "534"  "489"  "567"  "534"  "489"  "77"    "87"  
    "Lojas Hering" "567"  "534"  "489"  "567"  "534"  "489"  "77"    "87"  
    "Ideiasnet"    "820"  "656"  "573"  "0"    "186"  "287"  "741"   "789"
    "IGB S/A"      "877"  "827"  "780"  "0"    "0"    "0"    "17577" "4282"
    "Aliperti"     "1074" "4074" "9114" "1074" "4074" "9114" "2404"  "4637"
    "Aliperti"     "1074" "4074" "9114" "1074" "4074" "9114" "2404"  "4637"
    "Hercules"     "1098" "1102" "895"  "916"  "998"  "783"  "42"    "24"  
    "Hercules"     "1098" "1102" "895"  "916"  "998"  "783"  "42"    "24"  
    "J B Duarte"   "1145" "1145" "1144" "0"    "0"    "0"    "734"   "1088"
    "J B Duarte"   "1145" "1145" "1144" "0"    "0"    "0"    "734"   "1088"
    end
    destring _all, replace
    bysort Nome: gen ordem=_n
    reshape wide CtaRecCPDez CtaRecCPSet CtaRecCPJun ClieCPDez ClieCPSet ClieCPJun FornecDez FornecSet, i(Nome) j(ordem)

    Comment


    • #3
      Asked and answered in this post already: https://www.statalist.org/forums/for...apping-to-long

      Code:
      ren * value*
      ren valueNome Name
      reshape long value, i(Name) j(varname) string
      
      *get out the dates
      gen year = substr(varname,-4,.)
      replace varname = substr(varname,1,strlen(varname)-4)
      gen month = substr(varname,-3,.)
      replace varname = substr(varname,1,strlen(varname)-3)
      
      *probably followed by:
      reshape wide value, i(Name year month) j(varname) string
      ren value* *

      Comment


      • #4
        Jorrit, the code is not working. I´m getting this error for the first reshape.

        Code:
        variable valueAplFinDez2001 type mismatch with other value variables
        r(198);

        Comment


        • #5
          That suggests that some of your variables are string variables and some are numeric. Possibly because the variable has observations like "n/a" or other non-numeric values mixed in.
          Find the values that are causing problems, and think about if you can destring them them without any loss of data. Only when the variables to reshape long are of a similar type can you reshape them to (doubly) long.

          Note that in your example, all variables are of a string format, even though they contain only numeric info. Should they really be all numeric? Or is there a mix of numeric and string values needed?

          Code:
          clear
          input str13 Nome str10(CtaRecCPDez2017 CtaRecCPSet2017 CtaRecCPJun2017 ClieCPDez2017 ClieCPSet2017 ClieCPJun2017 FornecDez2017) str9 FornecSet2017
          "Nordon Met"   "500"  "496"  "493"  "0"    "0"    "0"    "382"   "125"
          "Lojas Hering" "567"  "534"  "489"  "567"  "534"  "489"  "77"    "87"  
          "Lojas Hering" "567"  "534"  "489"  "567"  "534"  "489"  "77"    "87"  
          "Ideiasnet"    "820"  "656"  "573"  "0"    "186"  "287"  "741"   "789"
          "IGB S/A"      "877"  "827"  "780"  "0"    "0"    "0"    "17577" "4282"
          "Aliperti"     "1074" "4074" "9114" "1074" "4074" "9114" "2404"  "4637"
          "Aliperti"     "1074" "4074" "9114" "1074" "4074" "9114" "2404"  "4637"
          "Hercules"     "1098" "1102" "895"  "916"  "998"  "783"  "42"    "24"  
          "Hercules"     "1098" "1102" "895"  "916"  "998"  "783"  "42"    "24"  
          "J B Duarte"   "1145" "1145" "1144" "0"    "0"    "0"    "734"   "1088"
          "J B Duarte"   "1145" "1145" "1144" "0"    "0"    "0"    "734"   "1088"
          end
          describe Clie*
          
          destring Cta* Clie* Fornec*, replace force
          
          describe Clie*

          Comment


          • #6
            Jorrit, the first reshape workout fine. But I'm having some problems extracting the dates. More specifically, extracting the months. It's generating missing varnames and dates, but the values still there.

            Code:
            . gen month = substr(varname,-3,.)
            (1,332 missing values generated)

            Code:
            * Example generated by -dataex-. To install: ssc install dataex
            clear
            input str13 Name str17 varname str18 value str4 year str3 month
            "524 Particip" "TotEmFiCP" "0"        "2017" "Mar"
            "524 Particip" "TotEmFiCP" "0"        "2017" "Jun"
            "AES Elpa"     ""          "7053105"  ""     ""   
            "AES Elpa"     ""          "9757322"  ""     ""   
            "AES Elpa"     ""          "2280135"  ""     ""   
            "AES Elpa"     ""          "4608054"  ""     ""   
            "AES Elpa"     "AplFin"    "-"        "2001" "Dez"
            "AES Elpa"     "AtivoTot"  "12797193" "2001" "Dez"
            "AES Elpa"     "AtvCir"    "2433538"  "2001" "Dez"
            "AES Elpa"     "CPV"       "0"        "2001" "Dez"
            "AES Elpa"     "ClieCP"    "1523810"  "2001" "Dez"
            end

            Comment


            • #7
              I do not really have an idea what is going on there. Almost as if there were variables with no name before reshape, which isnt possible.
              Can you, prior to the reshape, find out what the variable names were, corresponding with the observations:

              * Example generated by -dataex-. To install: ssc install dataex
              clear
              input str13 Name str17 varname str18 value str4 year str3 month
              "AES Elpa" "" "7053105" "" ""
              "AES Elpa" "" "9757322" "" ""
              "AES Elpa" "" "2280135" "" ""
              "AES Elpa" "" "4608054" "" ""
              end

              Comment


              • #8
                I found the problem. I had a few double variables. I just dropped them and worked out real nice.

                Thanks a lot Jorrit.

                Comment

                Working...
                X