DateTime strings Excel-to-Stata

[paulfgross]

I am using Stata ic 13.1 for mac 64 bit Intel

I imported into Stata a csv file with 4083 patient responses recorded at an import_date. The recorded format of that date in Excel is DD/MM/YY h:mm, and is shown as a str13 variable

My attempt to get an SIF date as DD/MM/YY in stata followed the sequence below

1. I first stripped off the h:mm in import_date

gen Time=substr(import_time,1,8)
list Time in 1/10
* +----------+

* 1. | 31/05/13 |
* 2. | 28/05/13 |
* 3. | 21/03/13 |
* 4. | 30/04/13 |
* 5. | 13/05/13 |
* |----------|
* 6. | 28/05/13 |
* 7. | 17/04/13 |
* 8. | 30/04/13 |
* 9. | 16/05/13 |
* 10. | 30/04/13 |
* +----------+
2. This new format seems OK but Time is shown as a str9 variable with format %9s. I tried

gen ImportTime = date(Time, "DMY")
format ImportTime %td

The result was
*(4083 missing values generated)


3. Noting that some days were 1-9, I tried adding an extra 0 in front of the day in import_date using

*replace import_time = "0" + import_time if length(import_time) == 7

4. To no avail as the 4083 missing values message was repeated when I recalculated ImportTime

I am missing something in my reading of stata dates and times or in importing excel csv to Stata.Any ideas?

paulfgross

[Nick Cox]

Note that this was answered on old Statalist. start at http://www.stata.com/statalist/archi.../msg00251.html

[Joseph Coveney]

You'll be better off posting your questions in the General forum. To answer your question, you need to use the topyear option of the date() function. See the documentation. Now I've got a question for you: why don't you use an ISO 8601 international standard format for dates?

.version13.1

.
.clear*

.setmoreoff

.
.inputstr9Time

Time
1."31/05/13"
2."28/05/13"
3."21/03/13"
4."30/04/13"
5."13/05/13"
6."28/05/13"
7."17/04/13"
8."30/04/13"
9."16/05/13"
10."30/04/13"
11.end

.
.generateintImportTime=date(Time,"DMY",2099)//<-here

.formatImportTime%td

.list,noobs

+----------------------+
|TimeImportT~e|
|----------------------|
|31/05/1331may2013|
|28/05/1328may2013|
|21/03/1321mar2013|
|30/04/1330apr2013|
|13/05/1313may2013|
|----------------------|
|28/05/1328may2013|
|17/04/1317apr2013|
|30/04/1330apr2013|
|16/05/1316may2013|
|30/04/1330apr2013|
+----------------------+

.
.exit

endofdo-file