Bookmark and Share

Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down on April 23, and its replacement, statalist.org is already up and running.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: st: AW: Date conversion in batch


From   Simon <scmoore.lists@googlemail.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: AW: Date conversion in batch
Date   Mon, 19 Apr 2010 11:01:07 +0100

On 18/04/2010 16:26, Martin Weiss wrote:
Can you give an example what your problem looks like? So you got to the
point that you are dealing with dates that are formatted inconsistently. Can
you provide an example dataset via -input- that makes the resulting mess
clearer to the list?

Certainly

In excel the various formats include:
1 - 01-Sep-84
2 - 01-Jan-08
3 - 20080214
4 - 3 Jan 2008 00:00:00
5 - 39796 (which is excel-speak for 14/12/2008 but stored as text)

If I try to convert using stat-transfer and append each the last one (5), as a string, elicits "date is str5 in using data"

input id double date
1	9010
1	9784
2	17532
2	17533
3	2.01e+07
3	2.01e+07
4	17534
4	17536
end

One way I can get all formats into stata is to convert each to a string and append

input id str10 date
1	9010
1	9784
2	17532
2	17533
3	20080214
3	20080217
4	17534
4	17536
5	39796
5	39774
end

All I know about these data is that they are dates. It would seem that the excel and Stata formats might overlap making it difficult to discern between the two. Otherwise I was thinking of pattern matching in some way for the 20080101 or 2008/01/01 type of date. Such as reading the data in as strings and searching for certain patterns.

Simon



On 18/04/2010 16:26, Martin Weiss wrote:

<>



Can you give an example what your problem looks like? So you got to the
point that you are dealing with dates that are formatted inconsistently. Can
you provide an example dataset via -input- that makes the resulting mess
clearer to the list?



HTH
Martin

-----Ursprüngliche Nachricht-----
Von: owner-statalist@hsphsun2.harvard.edu
[mailto:owner-statalist@hsphsun2.harvard.edu] Im Auftrag von Simon
Gesendet: Sonntag, 18. April 2010 14:34
An: statalist@hsphsun2.harvard.edu
Betreff: st: Date conversion in batch

Dear Statalist,

I have been sent a load of excel files that I want to combine into one
Stata file for analysis. First I use Roger Newson's stcmd with
Stat-transfer to convert the files to Stata files:

clear
cd "`dir'"
stcmd *.xls *.dta /y

and then use Nick Cox's -fs- to

set obs 0
g age = .
g date = ""

fs *.dta
foreach f in `r(files)' {
local fn = "`dir'\`f'"
append using "`fn'"
}

My problem is that date, coming from excel, is all over the place.
There's the text version of dd/mm/yyyy and the excel version of
dd/mm/yyyy and then various other formats including ddmmyy and ddmmyyyy.

Just wondering if anyone had come across something similar and whether
there is an easy solution - or am I destined to do this all by hand?

Simon


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/


© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   Site index