No of decimal positions via ODBC connection

[HeatherNewton]

Hi I have a file uploaded to a big data platform showing correct d.p but somehow when our clients see the same file via ODBC, the decimal place change from 4 to 2, how did that happen? Do I need to setup schema for showing data via ODBC or there is specific settings required dealing with?

[Patrick]

We can't know the details of your environment. You need to tell us. For example what is this "big data platform"? Some hadoop cluster, a database, ???

From a helicopter perspective: Sounds like a display issue either caused by the table definition (DDL) or by the client application surfacing the data.

If you access the uploaded file via SAS what do you see (using the best32. format).

[HeatherNewton]

oh I just tried to use format best32.4 but it shows that the informat is 21.2

so somewhere the informat was already setup? I didnt know ODBS has preset schema for tables..

[Patrick]

I didnt know ODBS has preset schema for tables..

It doesn't.

Please show us the code you use for loading the table into the "big data platform". Please also share the libname and/or connect statement. 

If the source table is SAS then please also share a Proc Contents for this table and tell us with which variable you've got the challenge.

[HeatherNewton]

 I checked all the way before sas

1. database   2. domain   3. interface

1. database - where the required table is stored

2. domain - where text file output from the required table in database is located 

3. interface - this interface is between domain in 2 and sas  (this interface is something related to setting up odbc)

all 1, 2,3 are showing 4d.p., so it is sas that is the problem

the IT guy who checked the interface confirmed it is showing 4d.p. and advised us to check setting in sas

how to check?

[Patrick]

1. database - where the required table is stored

- What is this "database"

2. domain - where text file output from the required table in database is located 

- I don't understand what the term "domain" could mean in the context of a storage location for a text file. And how is that related to the database?

3. interface - this interface is between domain in 2 and sas  (this interface is something related to setting up odbc)

- I must assume this is just the ODBC component connecting to "some database or the like"

all 1, 2,3 are showing 4d.p., so it is sas that is the problem

How is the text file sh

owing "4d.p"? 

From what you wrote (but you still haven't confirmed this!) I must assume the data you access is stored in Hadoop as a text file. If so then it's:

text file -> HIVE (the "table schema") -> ODBC -> SAS

Please show some code how you access the "table". 

chatGPT has given me below architecture which somehow looks right. SAS is the 3rd Party Application, the text file would be stored under HDFS.

As a next step in trying to determine what you're actually dealing with please share the SAS log and report when using below code (of course using your actual libname/libref):

libname myref odbc <connection info>;

options sastrace=',,,d' sastraceloc=saslog nostsuffix;
libname myref list;
data work.test;
  set myref.<table name>;
  stop;
run;

proc contents data=work.test;
run;quit;

proc options group=sql;
run;

[HeatherNewton]

the database is db2

the domain, is just somewhere this text file from db2 is stored, I dont think it is hadoop as out system is at least 30 years behind

It is like IT system create required files in databse then output as text to this domain where downstream system or  users subscribe for these files for later use

[Patrick]

---

@HeatherNewton wrote:

the database is db2

the domain, is just somewhere this text file from db2 is stored, I dont think it is hadoop as out system is at least 30 years behind

It is like IT system create required files in databse then output as text to this domain where downstream system or  users subscribe for these files for later use

---

You can't connect directly to a text file via ODBC. ODBC connects applications (like allowing SAS and DB2 to communicate via ODBC with each other).
To WHICH application is SAS connecting to via ODBC where displayed precision is not as expected? That's the bit which needs investigation first. 

A lot of things could be clarified if you run the code and then share the log and proc contents report I've asked for.

[HeatherNewton]

unfortunately I cannot run SAS myself, I will ask a colleague to and get back to you but may be a bit later as he is on vacation

[HeatherNewton]

what does <connection info> look like here? username followed by password? or some kind of strings?

[Patrick · Accepted solution]

---

@HeatherNewton wrote:

what does <connection info> look like here? username followed by password? or some kind of strings?

---

Just all the info you need to provide in addition to libref and the ODBC keyword. 

Under docu  LIBNAME Statement for the ODBC Engine you can also find sample code like:

libname mydblib odbc user=myusr1 password=mypwd1 datasrc=mydatasource;

[HeatherNewton]

I am not sure what they are as I dont set them up. But I found the file odbc.ini as attached, does this help? all the password is XXXXXX though

[HeatherNewton]

actually the people who set up the odbc confirmed they can show 4d.p. and ask me to check sas setting. what kind of setting does he mean, where do we have prior format setting in SAS?

[Patrick]

Using SAS is there a libname defined to the database with the table where you don't see the variable in the expected precision? If not then please write such a libname statement that YOU can execute. 

Then please share with us the output of a Proc Contents for this table. Some code you run along the line of below: 

libname yourlib ODBC .....;
proc contents data=yourlib.<table name>(keep=<varname under investigation>);
run;

[Tom]

---

@HeatherNewton wrote:

actually the people who set up the odbc confirmed they can show 4d.p. and ask me to check sas setting. what kind of setting does he mean, where do we have prior format setting in SAS?

---

Can you explain what "4d.p." means?

Note that if your variables in the external database are using DECIMAL numbers (fixed number of decimal digits) then SAS does NOT have anything that is equivalent.  All SAS numbers are stored as 64-bit binary floating point values.   Note that some decimal fractions, like 0.3 which is 3/10, cannot be exactly represented in binary fractions.