odbc

[HeatherNewton]

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?

[Tom]

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.

[HeatherNewton]

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

[Patrick]

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;

[HeatherNewton]

Request the DDL (table definition) from the Sybase DBA --- what do you mean by this? this DDL is in SAS, relates to SAS? 

or you mean from DB2 where pd was created? PD is defined as (8,4) in DB2

and also all the subsequent platform the files go through has capabilities to show 3 decimal places

[Patrick]

PD is defined as (8,4) in DB2

No it's not! It could be DECIMAL(8,4)

and also all the subsequent platform the files go through has capabilities to show 3 decimal places

What does that mean now? Are you looking at the table in DB2 or somewhere else.

What does step2 show you? Is it the actual internal value in the SAS table that only got two decimals or is it just a display issue?

IF the internal value got only two decimals then my next question would be how big these numbers actually are. If they exceed the number of digits SAS can store with full precisions then some of the decimal places could get lost. But assuming PD stands for Probability of Default that's unlikely the case.

[HeatherNewton]

I mean we created it as decimal(8,4) in db2

then sent as text with same decimal places which is then uploaded to another platform which is also showing 4 decimal places

and then it again got to sent to another platform which is also showing 4 decimal places

all the way is correct

until it reach SAS after ODBC

so I need to know if there is any setting in ODBC that rearrange it to 2 decimal places

as it is showing 21.2 for format and informat as shown in proc content

[HeatherNewton]

tries 1 shows 2 decimal places only

[Patrick]

Only test 2 will give you full certainty but assuming it confirms that the internal value got only stored with two decimals then you need now to look backwards one step at a time.

You only ever confirm half of the things I'm asking you which makes it really hard to know what has been done - but this is really important to decide what next to investigate.

Sooo.... for the Proc Contents did you run code similar to below?

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

If so then the next step is to confirm that the value on the SAS side stored with two digits really got a precision of four digits on the DB2 side. This can be done using explicit passthrough SQL where you cast the number into a character variable. Then on the SAS side load both PD and the new character variable with the PD value. If they are different then it's ODBC that doesn't do the right thing. ....and it's the ODBC driver then next thing would be to verify that a version gets used documented as suitable for SAS.  Option SASTRACE let's you write in detail the ODBC commands - but that's rather involved and something you better raise with SAS TS. ...but first one step at a time which means:

1. Run Test 2 as explained earlier

2. Run the explicit passthrough SQL with casting the PD variable and share the result

3. Verify the the ODBC driver version is what's required and documented as suitable for SAS (eventually already contact SAS Tech Support for this step).

[HeatherNewton]

Hi Patrick,

Thank you very much for your help. I was not able to follow your method where you provide below as I dont know what to put after ODBC

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

What my user did was just saved the files to a newly created library and proc content from there. Every entries show only 2 decimal places.

I have checked all the platforms before the files reach SAS all were able to display 4decimal places. That is why I think it is to do with ODBC set up or SAS setup.

[Tom]

If you don't know how to make a libref that points to your database then perhaps you have pointed it to the wrong database.

The only other possibility is that your ODBC driver is incorrect and it is having trouble telling SAS how the variables are defined in the database.  From the paths show in the photograph you posted of your monitor it looks like you are using a SYBASE database.  Make sure you are using the right ODBC driver for connecting to that type of database.

You might have better luck opening a ticket with SAS support.

[HeatherNewton]

oh I checked with user, actually they dont need to use any sas code, it is already saved in a library available

the strange thing is I have over 30 files and only this one field from 2 files has precison issue

[HeatherNewton]

this is how the data shows up on SAS enterprise guide 

[Patrick]

Given the screenshot what prevents you to run code very similar to below (which is one of the things I've been asking you to do already)?

proc sql;
  connect using <libref for SASApp-CRM-ODBC> as db2;
  create table work.investigate as
  select pd as pd format=best32., pd_char 
  from connection to db2
  (
    select pd, cast(pd as char(21)) as pd_char
    from tb_bpe_monthly_bsa_fullbase 
  );
quit;

If variable pd_char also only shows two decimals then the issue is on the DB side with the number in PD just stored in this precision, else if pd_char shows 4 decimals and pd only 2 decimals then the issue is potentially with the ODBC driver (would need further investigation).

Do I get that right that you yourself don't have access to the environment but just pass-on everything to your client for execution? If so: Don't they have a SAS Admin who can investigate this?

...and before you ask: You should be able to get the libref for SASApp-CRM-ODBC by right clicking on the library object and select properties in the drop down menu. 

[HeatherNewton]

yes I only pass to user to execute

no particular sas admin in this place

may be they think I am now

Thank you Patrick for your kind support!