Not able to convert string to date using date()

[Jonathan Nathan]

Hi,

I am having trouble converting a string to a date.

I have imported an excel file, and have a variable Period which lists string values of the form "DDMMYYYY". However, I cannot seem to be able to convert it to an date. I have used the date function to convert the string, however all that I get are "." as the resulting variable.

I am using publicly available data at: https://www.apra.gov.au/sites/defaul...june_2018.xlsx


I then import using:

import excel "C:\****\annual_mysuper_statistics_back_series_june_2018.xlsx", sheet("Table 2") cellrange(A7:EX948) firstrow
drop in 1/2
keep in 1/563

Period is being stored has a string type of %str181 and format of %s181.

Using the date function:

gen Date=date(Period, "DMY")

i only get :

 gen Date=date(Period, "DMY")
(563 missing values generated)

. li Date

     +------+
     | Date |
     |------|
  1. |    . |
  2. |    . |
  3. |    . |
  4. |    . |
  5. |    . |
     |------|
  6. |    . |
  7. |    . |

I am not sure what I've done wrong or how to proceed forward, so any help would be appreciated!

[Liu Qiang]

can you please offer some raw data?

[Nick Cox]

str181 should alert you to a problem. Whatever you have in there is more than DD/MM/YYYY. Try importing directly from the internet source.

EDIT: A574 is junk as far as dates are concerned. This worked for me:

 import excel using https://www.apra.gov.au/sites/default/files/annual_mysuper_statistics_back_series_june_2018.xlsx, sheet("Table 2") cellrange(A7:A572) firstrow

. tab Period

     Period |      Freq.     Percent        Cum.
------------+-----------------------------------
  30jun2014 |        117       20.78       20.78
  30jun2015 |        116       20.60       41.39
  30jun2016 |        115       20.43       61.81
  30jun2017 |        111       19.72       81.53
  30jun2018 |        104       18.47      100.00
------------+-----------------------------------
      Total |        563      100.00

. d

Contains data
  obs:           565                          
 vars:             1                          
 size:         1,130                          
--------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
--------------------------------------------------------------------------------------
Period          int     %td..                 Period
--------------------------------------------------------------------------------------
Sorted by: 
     Note: Dataset has changed since last saved.

. l if missing(Period)

     +--------+
     | Period |
     |--------|
  1. |      . |
  2. |      . |
     +--------+

[Jonathan Nathan]

Thanks Nick!!

I really appreciate the response!!

Also, been a long time observer of your responses, but this is my first time actually posting on Statalist and just want to say thanks and you are an absolute legend!