Re: st: AW: Date conversion in batch

[Simon <scmoore.lists@googlemail.com>]

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/