I have banned excel for data entry in our group - particualry if dates are involved! MSAccess will handle your data in a much better way and you can export to a text file then import to stata. Access will allow missing data data to be represented as 01/01/9999.
Richard
>>>
[email protected] 8/05/2006 6:42:23 pm >>>
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
............to 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
http://www.epidata.dk .
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
_________________________________________
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
Email:
[email protected] __________________________________________
*
* For searches and help try:
*
http://www.stata.com/support/faqs/res/findit.html *
http://www.stata.com/support/statalist/faq *
http://www.ats.ucla.edu/stat/stata/ 
