BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
HeatherNewton
Quartz | Level 8

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 PDproc content.jpg

what governs the output format in sas via odbc?

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

 

 

Tom
Super User Tom
Super User

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
Quartz | Level 8

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
Opal | Level 21

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
Quartz | Level 8

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
Opal | Level 21

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
Quartz | Level 8

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
Quartz | Level 8

tries 1 shows 2 decimal places only

 

Patrick
Opal | Level 21

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
Quartz | Level 8

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
Super User Tom
Super User

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
Quartz | Level 8

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
Quartz | Level 8

this is how the data shows up on SAS enterprise guide unnamed.jpg

Patrick
Opal | Level 21

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
Quartz | Level 8

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!

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 45 replies
  • 2772 views
  • 0 likes
  • 5 in conversation