Import 'dd.mm.yy' format date to Stata

[Francois Durant]

Hi,

I would like to import the following dates "Date" to Stata date format. Anyone has delt with this format before? Thanks.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 Date double FX_vola
"'28.02.85'" .00748233664630325
"'29.03.85'" .00798176898598356
"'30.04.85'" .00682847781736855
"'31.05.85'" .00574453001916299
"'28.06.85'" .00477597861847206
"'31.07.85'" .00580222898752755
"'30.08.85'" .00492636910452643
"'30.09.85'"  .0076178626633199
"'31.10.85'" .00377851419983126
"'29.11.85'" .00469109649661885
"'31.12.85'" .00456508510401863
"'31.01.86'" .00462907357814557
"'28.02.86'" .00647081093872982
"'31.03.86'" .00538535118819888
"'30.04.86'" .00579795092842141
"'30.05.86'" .00551990960091331
"'30.06.86'" .00539859638407607
"'31.07.86'" .00576344304299958
"'29.08.86'" .00410378064567503
"'30.09.86'" .00470899423174998
"'31.10.86'" .00380799988015137
"'28.11.86'" .00324725823962322
"'31.12.86'" .00363551275764214
"'30.01.87'" .00576638541609231
"'27.02.87'" .00449269385332392
"'31.03.87'" .00354293105667831
"'30.04.87'" .00414387597729534
"'29.05.87'" .00339352421882873
"'30.06.87'" .00400907793836038
"'31.07.87'" .00326013702614298
"'31.08.87'" .00388667657014239
"'30.09.87'" .00333337869703639
"'30.10.87'" .00523846671190995
"'30.11.87'" .00562482806337552
"'31.12.87'" .00375929894300394
"'29.01.88'" .00558199685339998
"'29.02.88'"  .0033357479590664
"'31.03.88'" .00359319655487261
"'29.04.88'" .00317170097707722
"'31.05.88'" .00272754668738617
"'30.06.88'"  .0048647508655167
"'29.07.88'" .00643804380181451
"'31.08.88'" .00446328279894478
"'30.09.88'" .00374041919501475
"'31.10.88'" .00363348175515974
"'30.11.88'" .00464171999103633
"'30.12.88'" .00440099381314651
"'31.01.89'" .00462902172042191
"'28.02.89'" .00422014684500774
"'31.03.89'" .00292542958061131
"'28.04.89'" .00347860248100428
"'31.05.89'"  .0054213232595246
"'30.06.89'"  .0059157132244139
"'31.07.89'" .00543226127518073
"'31.08.89'" .00480661868457262
"'29.09.89'" .00514265760017469
"'31.10.89'" .00387713157762824
"'30.11.89'" .00266059438162366
"'29.12.89'" .00287479372215855
"'31.01.90'" .00448319660773311
"'28.02.90'" .00335272227109393
"'30.03.90'" .00378508133395132
"'30.04.90'" .00272778484256595
"'31.05.90'" .00331634246260906
"'29.06.90'" .00296426907680685
"'31.07.90'" .00402557322370179
"'31.08.90'" .00463524538907253
"'28.09.90'" .00528242447697455
"'31.10.90'" .00467044281722238
"'30.11.90'" .00357281876243052
"'31.12.90'" .00416155692550756
"'31.01.91'"  .0045465999542586
"'28.02.91'" .00392675352914474
"'29.03.91'"  .0049776269856419
"'30.04.91'" .00594470193673816
"'31.05.91'" .00479800902755284
"'28.06.91'" .00437412216989296
"'31.07.91'"  .0044321433468653
"'30.08.91'" .00474847788547848
"'30.09.91'" .00363068984976254
"'31.10.91'" .00351844794049125
"'29.11.91'" .00391720332319088
"'31.12.91'" .00338434880930233
"'31.01.92'" .00695851300997201
"'28.02.92'"  .0042380969737464
"'31.03.92'" .00421013412130035
"'30.04.92'" .00328247758235496
"'29.05.92'" .00390959808767493
"'30.06.92'" .00315461627634077
"'31.07.92'" .00434433572223456
"'31.08.92'" .00369692277967712
"'30.09.92'" .00751929084905579
"'30.10.92'" .00621562372415908
"'30.11.92'" .00618898453440336
"'31.12.92'" .00484428448314461
"'29.01.93'" .00557419062407668
"'26.02.93'" .00480273827540238
"'31.03.93'" .00486093049940336
"'30.04.93'" .00509774238379313
"'31.05.93'"  .0043526517372228
end

[Tarun Choudhary]

gen wanted = year(dofc(datadate))

So, you will see that I don't guess the same as Tarun Choudhary. Tarun, we guess differently at what Ahmed has, but assigning numeric daily dates a display format that shows the years won't stop them being spaced 365 or 366 days apart.

Nick Cox Thank you for the clear explanation. I agree that modifying the display format was not the right approach.

[Francois Durant]

Thanks a lot Nick!

[Awais Ahmed]

Dear @Nick Cox and Tarun Choudhary.. Thank you for your responses. I have requested to change my real name and I hope it will be corrected, apologizes.

The code:
gen wanted = year(dofc(datadate)) works perfect on my dataset and it generated the years from the datadate. Thanks again! Ahmed.

[Nick Cox]

help please Please follow https://www.statalist.org/forums/help#realnames and re-register with a real name.

You seem very confused about datetimes and associated datetime display formats. There is no easy way here other than reading the help carefully, skimming and skipping whatever seems irrelevant. Alternatively, if you get completely lost, you need to give us completely clear data examples. https://www.statalist.org/forums/help#stata explains how to give data examples.

A reader has to struggle to guess whether your datadate variable is a string variable (which is implied by attempts to push it through clock() ) or already a numeric datetime variable. (which is implied by attempts to assign it a different display format).

I guess your type mismatch error arises because datadate is already numeric, in which case recasting the format won't change its value. To extract years from datetime variables, you need to push them through year(dofc())

Accordingly, my best guess is

gen wanted = year(dofc(datadate))

So, you will see that I don't guess the same as Tarun Choudhary. Tarun, we guess differently at what Ahmed has, but assigning numeric daily dates a display format that shows the years won't stop them being spaced 365 or 366 days apart.

[Tarun Choudhary]

This should work

gen dt = date(datadate, "DM20Yhms")
format dt %tdCCYY

[Awais Ahmed]

Hi Nick. I have more or less similar issue with datadate variable . Variable datadate 's format is like : 31oct2000 00:00:00, 31oct2001 00:00:00 , 31oct2002 00:00:00 .... ....................................31oct2020 00:00:00

The datadate variable is from 2000 to 2020 for different months. I wanted to keep only years . I tried changing formats of my variable and use below code but does not any give me years 2000 to 2020. Error i get is type mismatch!

format datadate %tdCCYY.NN.DD
generate double dt = clock(datadate, "# MD hms # Y")


format datadate %tdMonth dd, CCYY
generate double dt = clock(datadate, "# MD hms # Y")


format datadate %tdnn/dd/YY
generate double dt = clock(datadate, "# MD hms # Y")


format datadate %tcCCYY.NN.DD HH:MM
generate double dt = clock(datadate, "# MD hms # Y")


format datadate %tcMonth dd, CCYY hh:MM am
generate double dt = clock(datadate, "# MD hms # Y")


format datadate %tcDay Mon DD HH:MM:SS CCYY
generate double dt = clock(datadate, "# MD hms # Y")

Do you have advice please? Thank you so much!

Ahmed

[Nick Cox]

The only thing unusual here are the single quotes. Otherwise the format is entirely straightforward given a reading of help datetime.

. gen dailydate = daily(subinstr(Date, "'", "", .), "DMY", 2021)

. format dailydate %td 

. 
. list *ate 

     +------------------------+
     |       Date   dailydate |
     |------------------------|
  1. | '28.02.85'   28feb1985 |
  2. | '29.03.85'   29mar1985 |
  3. | '30.04.85'   30apr1985 |
  4. | '31.05.85'   31may1985 |
  5. | '28.06.85'   28jun1985 |
     |------------------------|
  6. | '31.07.85'   31jul1985 |
  7. | '30.08.85'   30aug1985 |
  8. | '30.09.85'   30sep1985 |
  9. | '31.10.85'   31oct1985 |
 10. | '29.11.85'   29nov1985 |
     |------------------------|
 11. | '31.12.85'   31dec1985 |
 12. | '31.01.86'   31jan1986 |
 13. | '28.02.86'   28feb1986 |
 14. | '31.03.86'   31mar1986 |
 15. | '30.04.86'   30apr1986 |
     |------------------------|
 16. | '30.05.86'   30may1986 |
 17. | '30.06.86'   30jun1986 |
 18. | '31.07.86'   31jul1986 |
 19. | '29.08.86'   29aug1986 |
 20. | '30.09.86'   30sep1986 |
     |------------------------|
 21. | '31.10.86'   31oct1986 |
 22. | '28.11.86'   28nov1986 |
 23. | '31.12.86'   31dec1986 |
     +------------------------+