AW: st: AW: Date conversion in batch

["Martin Weiss" <martin.weiss1@gmx.de>]

<> 

Make the line -gen int newdate=date(date, "YMD") if length(date)>=8-

*************
gen int newdate=date(date, "YMD") if length(date)==8
*************

If "date" is indeed longer than 8, there might be other stuff lurking in
there that your example data do not contain...


HTH
Martin


-----Ursprüngliche Nachricht-----
Von: owner-statalist@hsphsun2.harvard.edu
[mailto:owner-statalist@hsphsun2.harvard.edu] Im Auftrag von Martin Weiss
Gesendet: Montag, 19. April 2010 13:25
An: statalist@hsphsun2.harvard.edu
Betreff: AW: st: AW: Date conversion in batch


<> 

I could see you peeling this thing off one by one, as in:


*************
clear*

inp byte id str8 date
1	"9010"
1	"9784"
2	"17532"
2	"17533"
3	"20080214"
3	"20080217"
4	"17534"
4	"17536"
5	"39796"
5	"39774"
end

gen int newdate=date(date, "YMD") if length(date)>=8
format newdate %tdDD/NN/CCYY

replace newdate=real(date) if substr(date, 1, 2)=="17"

replace newdate=real(date)-21916 if substr(date, 1, 2)=="39"

list, noo
*************


You want to be very sure you set those -if- qualifiers right, though. Any
mistake here could spoil your fun big time somewhere down the line.


BTW, what do "9010" and "9784" stand for?



HTH
Martin

-----Ursprüngliche Nachricht-----
Von: owner-statalist@hsphsun2.harvard.edu
[mailto:owner-statalist@hsphsun2.harvard.edu] Im Auftrag von Simon
Gesendet: Montag, 19. April 2010 12:01
An: statalist@hsphsun2.harvard.edu
Betreff: Re: st: AW: Date conversion in batch

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/


*
*   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/