Announcement

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

  • Formatting a numeric date with two mixed numeric formats

    Dear members
    I have merged two dataset that contain two different numeric date format ( one contains about 9 numbers and the other contains 6 numbers) I would like to format this date into one proper numeric date format.

    thank you

  • #2
    Provide a data example using the dataex command.

    Comment


    • #3
      Thanks, Andrew. here is an example: the variable that I want to format is the firm_date
      nput str97 Company str12 ISIN_number int Year float firm_date
      "GrupoClarin" "ARGCLA010015" 2015 19990830
      "GrupoClarin" "ARGCLA010015" 2016 19990830
      "GrupoClarin" "ARGCLA010015" 2017 19990830
      "GrupoClarin" "ARGCLA010015" 2018 19990830
      "GrupoClarin" "ARGCLA010015" 2019 19990830
      "GrupoClarin" "ARGCLA010015" 2020 19990830
      "GrupoFincieroGaliciaS.A." "ARP495251018" 2018 19990930
      "GrupoFincieroGaliciaS.A." "ARP495251018" 2019 19990930
      "GrupoFincieroGaliciaS.A." "ARP495251018" 2020 19990930
      "YPFSA" "ARP9897X1319" 2010 19770206
      "YPFSA" "ARP9897X1319" 2013 19770206
      "YPFSA" "ARP9897X1319" 2014 19770206
      "YPFSA" "ARP9897X1319" 2015 19770206
      "YPFSA" "ARP9897X1319" 2016 19770206
      "YPFSA" "ARP9897X1319" 2017 19770206
      "YPFSA" "ARP9897X1319" 2018 19770206
      "YPFSA" "ARP9897X1319" 2019 19770206
      "TerniumArgentiS.A." "ARSIDE010029" 2018 19620308
      "TerniumArgentiS.A." "ARSIDE010029" 2019 19620308
      "StrabagSE" "AT000000STR1" 2010 19860100
      "StrabagSE" "AT000000STR1" 2011 19860100
      "StrabagSE" "AT000000STR1" 2012 19860100
      "StrabagSE" "AT000000STR1" 2013 19860100
      "StrabagSE" "AT000000STR1" 2014 19860100
      "StrabagSE" "AT000000STR1" 2015 19860100
      "StrabagSE" "AT000000STR1" 2016 19860100
      "StrabagSE" "AT000000STR1" 2017 19860100
      "StrabagSE" "AT000000STR1" 2018 19860100
      "StrabagSE" "AT000000STR1" 2019 19860100
      "PORRAG" "AT0000609607" 2016 18690100
      "PORRAG" "AT0000609607" 2017 18690100
      "PORRAG" "AT0000609607" 2018 18690100
      "PORRAG" "AT0000609607" 2019 18690100
      "ErsteGroupBankAG" "AT0000652011" 2009 20080100
      "ErsteGroupBankAG" "AT0000652011" 2010 20080100
      "ErsteGroupBankAG" "AT0000652011" 2011 20080100
      "ErsteGroupBankAG" "AT0000652011" 2012 20080100
      "ErsteGroupBankAG" "AT0000652011" 2013 20080100
      "ErsteGroupBankAG" "AT0000652011" 2014 20080100
      "ErsteGroupBankAG" "AT0000652011" 2015 20080100
      "ErsteGroupBankAG" "AT0000652011" 2016 20080100
      "ErsteGroupBankAG" "AT0000652011" 2017 20080100
      "ErsteGroupBankAG" "AT0000652011" 2018 20080100
      MERCK Kommanditg" "DE0006599905" 2016 -48577
      "MERCK Kommand " "DE0006599905" 2019 -48577
      "MERCK Kommand" "DE0006599905" 2008 -48577
      "MERCK Kommandi" "DE0006599905" 2017 -48577
      "MERCK Kommandit" "DE0006599905" 2018 -48577
      "MERCK Kommandit " "DE0006599905" 2009 -48577

      Comment


      • #4
        The first couple of dates seem to be written as YYYY-MM-DD so that "19990830" is 30th August 1999 and "19770206" is 6th February 1977. But I cannot make sense of "19860100". Also, what would -48577 represent? Did you import these data from Excel or somewhere else? What were the corresponding dates prior to import?

        Comment


        • #5
          Thanks for the data example. You've more problems than one. 19990830 is easy to interpret, but what are to make of 20080100 and other 8-digits dates ending in 00?

          Possibly

          1. A day element was never entered. Those are monthly dates with 00 as a filler.

          2. The original data had more detail, which is lost on import as float. You can't hold 20080101 exactly in float -- do try it!

          That said, this may help:

          Code:
          * Example generated by -dataex-. For more info, type help dataex
          clear
          input float firm_date
            -48577
          18690100
          19620308
          19770206
          19860100
          19990830
          19990930
          20080100
          end
          
          recast long firm_date  
          replace firm_date = firm_date + 1 if firm_date >= 1e7 & mod(firm_date, 100) == 0  
          replace firm_date = daily(strofreal(firm_date, "%10.0f"), "YMD") if firm_date >= 1e7
          gen mo_date = mofd(firm_date)
          format firm_date %td
          format mo_date %tm
          
          list
          
               +---------------------+
               | firm_date   mo_date |
               |---------------------|
            1. | 01jan1827    1827m1 |
            2. | 01jan1869    1869m1 |
            3. | 08mar1962    1962m3 |
            4. | 06feb1977    1977m2 |
            5. | 01jan1986    1986m1 |
               |---------------------|
            6. | 30aug1999    1999m8 |
            7. | 30sep1999    1999m9 |
            8. | 01jan2008    2008m1 |
               +---------------------+

          Comment


          • #6
            Thank you to both of you for your helpful response. Nick's code worked perfectly well. thanks again

            Comment

            Working...
            X