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

Re: st: Too many decimal places for ODBC

From   "Joseph Coveney" <>
To   "Statalist" <>
Subject   Re: st: Too many decimal places for ODBC
Date   Wed, 14 May 2008 23:54:47 +0900

Stefan Gawrich wrote:

I'm trying to establish ODBC-connections between Stata and a Firebird
database (V: 2.1) under Win XP.
The available ODBC-drivers are a bit outdated and not well documented. So I
had to find out more or less by trial and error why I couldn't export float
and double vars. The error is not a decimal point/comma issue but a issue of
decimal places.
The ODBC-driver is only capable of 18 decimal places while stata exports 20.


My question:

Is there any way to reduce decimal places before ODBC export inside Stata (I
need no more than 2 or 3 decimal places)

There are workarounds (like export as string or export multiplied with 1000
as an integer - with reconversion inside the database) but these are
burdensome and error-prone in routine use.


There are a couple of ways that come to mind.  The fastest is probably via
Kit Baum's -tosql- and then -odbc sqlfile-.  The illustration do-file below
uses Microsoft Access solely because it's handy for illustration.  I assume
that it would work with the DSN set up for Firebird.

Note that Access allows inserts of numeric values to be in single quote
marks, i.e., it allows a statement like

INSERT INTO s1 (age) VALUES ('6.123456789012345678')

when the column, age, is numeric.  The suggestion assumes that Firebird
does, too.

You can install -tosql- from SSC (-findit tosql-).

Joseph Coveney

clear *
set more off
set obs 2
generate double age = 6.00000000000000000000
replace age = 6.123456789012345678 in 2
local dsn MS Access Database;DBQ=db1.mdb;
* Begin here
tostring age, replace format("%20.18f")
tosql age, table(s1)
odbc exec("CREATE TABLE s1 (age FLOAT)"), dsn("`dsn'")
odbc sqlfile(s1.sql), dsn("`dsn'")

*   For searches and help try:

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