Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
From | "Martin Weiss" <martin.weiss1@gmx.de> |
To | <statalist@hsphsun2.harvard.edu> |
Subject | AW: st: AW: Date conversion in batch |
Date | Mon, 19 Apr 2010 13:25:00 +0200 |
<> 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/