Statalist The Stata Listserver

[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

Re: st: Importing dates from Excel

From   "Svend Juul" <SJ@SOCI.AU.DK>
To   <>
Subject   Re: st: Importing dates from Excel
Date   Mon, 8 May 2006 10:42:23 +0200

Hugh wrote:

I have 7 date columns in my master file in the "float" format and need
to constantly update the date info in the master file since some obs
have missing dates. To do this, I create a new using data file with
the updated dates and then merge into the master file. The problem is
that the using data file has dates in the "string" format since I
export dates from Excel in the mm/dd/yyyy format. So, each time I have
to go through 7 different iterations of the program below to change
date formats in the using data file so I can merge it correctly in the
master file in the "float" format.

For example, assume one of my 7 date variables in the master file is
called bdate. I then have to use

. generate bdate1 = date(bdate, "mdy")
    . format bdate1 %d
.rename bdate1 bdate change it into a "float" storage type.

I have to do this for each of the 7 dates everytime I want to update
missing date info in my master file.

Is there a faster way? Can I format the dates in Excel in such a way
that the using data file will import the dates correctly in the
"float" format? I need the date in the float format because I have to
calculate no of days between dates. My master file has dates in the
form "08apr2006", for example.

First: for this type of repetitive work you should make a do-file, so
that you only need to write the set of commands once.

Second: When repeating the same procedure for several variables, use
macros and the -foreach- command. In your case it could look like:

   foreach D of varlist bdate cdate ddate edate fdate {
     generate `D'1 = date(`D', "mdy")
     format `D'1 %d
     drop `D'
     rename `D'1 `D'

There are other issues, like:

Why do data come from Excel? Do you use Excel for entering data? There
are much better solutions to entering data; my recommendation is
EpiData, available for free from

How do you transfer data from Excel to Stata? Do you copy-and-paste?
This is a somewhat risky method; better to save a text file from Excel
and let Stata read it with the -insheet- command.

Hope this helps


Svend Juul
Institut for Folkesundhed, Afdeling for Epidemiologi
(Institute of Public Health, Department of Epidemiology)
Vennelyst Boulevard 6
DK-8000  Aarhus C, Denmark
Phone: +45 8942 6090
Home:  +45 8693 7796

*   For searches and help try:

© Copyright 1996–2017 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index