topic Re: odbc in SAS Programming

No of decimal positions via ODBC connection

HeatherNewton — Tue, 28 May 2024 12:04:57 GMT

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?

Re: odbc

Patrick — Tue, 07 May 2024 02:57:31 GMT

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).

Re: odbc

HeatherNewton — Tue, 07 May 2024 06:58:53 GMT

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..

Re: odbc

Patrick — Tue, 07 May 2024 08:04:29 GMT

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.

Re: odbc

HeatherNewton — Tue, 21 May 2024 06:33:22 GMT

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?

Re: odbc

Patrick — Tue, 21 May 2024 08:17:58 GMT

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;

Re: odbc

HeatherNewton — Tue, 21 May 2024 08:18:20 GMT

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

Re: odbc

Patrick — Tue, 21 May 2024 08:42:44 GMT

@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.

Re: odbc

HeatherNewton — Tue, 21 May 2024 08:50:54 GMT

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

Re: odbc

ballardw — Tue, 21 May 2024 13:49:38 GMT

@HeatherNewton wrote:

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..

Format is for display, Informat is for reading external data. The two do not have to have much in common. However if the value was reading only two decimals then showing 3 or more, which a format may specify, will basically show 0 unless the value was one that has problems with binary to decimal conversion. Setting a format will never change an informat or actual values of data as read. So I am not sure what your point about this format may be intended to be.

The BESTW.D version formats from the documentation:

The BESTDw.p format is a combination of the BESTw. format and the Dw.p format in that it formats all numeric data, and it does a better job of aligning decimals than the BESTw. format.

Alignment of decimal points may be a bit tricky in actual values displayed when a large range of values are involved.

Re: odbc

HeatherNewton — Mon, 27 May 2024 06:11:47 GMT

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

Re: odbc

Patrick — Mon, 27 May 2024 07:04:32 GMT

@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;

Re: odbc

HeatherNewton — Mon, 27 May 2024 07:54:26 GMT

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

Re: odbc

HeatherNewton — Mon, 27 May 2024 08:01:37 GMT

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?

Re: odbc

Patrick — Mon, 27 May 2024 08:46:51 GMT

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;

Re: odbc

Tom — Mon, 27 May 2024 16:04:31 GMT

@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.

Re: odbc

HeatherNewton — Tue, 28 May 2024 01:44:57 GMT

4 d.p. mean I need output in 4 decimal place

all input before SAS (from DB2 to text file in a platform to another big data platform) are all showing 4 decimal place but SAS is showing 2 decimal place only. I need to confirm if there are settings somewhere in SAS that has change it to 2 decimal places.

I attached proc content for your reference it is highlighted in yellow the variable PD

what governs the output format in sas via odbc?

the informat show here ? was there schema somewhere already setup somewhere?

Re: odbc

Tom — Tue, 28 May 2024 01:58:53 GMT

How did you MAKE that dataset? Please show the code.

I do not know of anything that would change a variable that is defined to store 4 digits after the decimal place to be displayed with only 2 digits after the decimal place.

SAS/Access to XYZ database will generally match the format attached to a variable (the INFORMAT for existing dataset is meaningless) to the variable definition in the remote database. So if the remote variable is designed to store N decimal digits with D of them occurring after the decimal point then in general that would translate to a value of N+1 for the Width of the format (SAS includes the decimal point in the width).

Note that 20 digits is MORE THAN SAS CAN STORE IN A NUMERIC VARIABLE.

If you are having a lot of trouble with the PD variable you could try using the DBSASTYPE= dataset option to have the value transferred as a character string instead.

Re: odbc

Patrick — Tue, 28 May 2024 02:22:18 GMT

Assuming you've run this Proc Contents using a libref that you defined via a libname statement (and not some pre-assigned libname that potentially uses a SAS metadata table object):

With any SAS/Access engine when reading a database table the variable type, format and informat is derived from the table definition in the database (DDL). In your case that could be something like DECIMAL(21,2). If that's the case then precision would already be lost on the DB side.

Next steps:

1. Assign format BEST32. to variable PD and inspect if any value shows with more than two decimals.

2. If no values with more than 2 decimals: Request the DDL (table definition) from the Sybase DBA so you can verify that you don't access a table where precision already is lost on the DB side.

...further steps depending on the result of 1) and 2)

For 1: If there are any rows in table Test then you've got values with more than 2 decimals.

data test;
  format pd best32.;
  set <yourlib>.<yourtable>(keep=pd);
  if 100*round(pd,0.0000000001) ne int(100*pd) then output;
run;

Re: odbc

HeatherNewton — Tue, 28 May 2024 02:24:48 GMT

pd is a between 0 and 1

it is basically equals total bad count/total active count e.g. 4/10 so 0.4

so it will never be 20 as we kept decimal place to 4 only in all the sources