Require solutions to Numeric precision issues(moving data between Oracle DBs through SAS)?

[PoornimaRavishankar]

Hi,

First things - Oracle 11g, SAS V9.3/9.4.

I see numerous threads, notes etc. on the numerical precision problems encountered while dealing with extremely large numbers.

I have a pass-through SQL accessing data in an Oracle DB and the problem starts right there. My program reads a field with 19 significant digits and there is a difference of '24' between the DB values and my SAS dataset values.

Then I upload the data into another Oracle DB and this time it rounds the last 4 digits off to the nearest thousand. I am using a PROC SQL INSERT and the dataset from which I do the insert shows only a difference of '24' but when it inserts into the DB, it rounds it off!

Is there a (easy ) solution for this which can be implemented within the SAS program? 

Thanks!!!

[SASKiwi]

What is the column type of the numeric in Oracle? One option would be to convert the column to character in SAS then convert it back again when loading back into Oracle. Please post an example of your code.  

[PoornimaRavishankar]

The source is numeric with 19 digits. The target is the same.

Converting back to numeric will do the same thing - result in loss of accuracy. Converting to numeric within SAS at any point will do this.

We are unable to change the DB definitions of the field to string. (If they were both strings, this problem would be solved)

I am expecting the solution to this if it exists, is a little complicated. 

[Reeza]

Out of curiousity, what kind of number has 19 significant digits?

[PoornimaRavishankar]

Some kind of transaction ID

[Reeza]

So it's not a numeric field that you'll be doing calculations with? 

Bad design issue then, you should actually push to have the DB changed to character, storing numbers in numeric fields that aren't numeric leads to issues in my experience.

[PoornimaRavishankar]

Sure does. Unfortunately, it is a third party DB that can't be changed by us. And IMO, the DB shouldn't be changed for the drawbacks of SAS. It is defined a certain way for a certain reason. I am not doing calculations with it, but probably someone else might use it in a different numerical capacity.

That SAS is incapable of handling such numbers, even simple extraction, which another technology(like Oracle or Informatica) can is something SAS should look at fixing.

I have raised a ticket with SAS Tech support and am waiting for their answer now.

[Reeza]

---

@PoornimaRavishankar wrote:

Sure does. Unfortunately, it is a third party DB that can't be changed by us. And IMO, the DB shouldn't be changed for the drawbacks of SAS. It is defined a certain way for a certain reason. I am not doing calculations with it, but probably someone else might use it in a different numerical capacity.

---

I don't disagree that SAS should be able to handle the number. I sort of understand why it doesn't but that's not relevant. If it is a transaction ID all you can do is count it and a character variable is just as easy as numerical variable. Programming your way around bad design happens all the time, still doesn't mean it's correct either. Its probably being stored as a number to save space...but it will run into issues with other application interfaces, not just SAS. 

SAS isn't a database application/software, so comparisons to Oracle or Informatica aren't relevant, it's Statistical Analysis Software.

Anyways, SAS Tech support is your best bet for help. 

[PoornimaRavishankar]

SAS may have started out for statistical anlayses but it being used purely for ETL in many, many places within the industry.  This is very basic extraction I am talking about. It is able to read data from many different DBs.

Also, what if I had said that the field contains the world population of some kind of micro organism and there millions of species for which similar data is stored in the DB. What if some kind of computation had to be done on the populations? 

I am sure you alone cannot possibly imagine all possible scenarios and uses for the software so if I may and with all due respect, I'd  be very happy to listen if you actually had something to say about the actual problem instead of getting around it by blaming the design of someone's else software, which many other APIs seem to have no problem with.

It does seem tech support is my best bet.

[Reeza]

http://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#n0v4ma1zb9lu99n172...

DBSASTYPE

You can specify the type when bringing it in, from the last example it seems to imply that the database will handle the conversion when it's uploaded. 

---

@PoornimaRavishankar wrote:

I am sure you alone cannot possibly imagine all possible scenarios and uses for the software so if I may and with all due respect, I'd  be very happy to listen if you actually had something to say about the actual problem instead of getting around it by blaming the design of someone's else software, which many other APIs seem to have no problem with.

And many other API's may not have issues, but many will. Especially, if someone ever forgets to check it because it usually doesn't generate errors. 

[PoornimaRavishankar]

Yes, thank you, that's the option I used but in a different way.. I have posted full solution below..

[PoornimaRavishankar · Accepted solution]

Thanks everyone for the replies. As most of the posts advised, DBSASTYPE is the option to use. This is the summary of what I did -

I have a passthrough SQL reading data from my source Oracle DB. So I used the to_char function in Oracle SQL to convert data into character while bringing it into SAS. Not specifying a format argument to this function results in a character string of length 40.

to_char(<fieldname>)

Once this is done and I do my transformation, I use a PROC SQL insert or PROC APPEND to load data into another Oracle DB  -

LIBNAME OCLTGT oracle user = xyz password = ***** path = <pathname> schema = <schemaname> ;

PROC APPEND BASE=OCLTGT.<tablename> (bulkload=yes
dbsastype=(y='CHAR(19)'
                    z='CHAR(22)')
                   )

BL_DELETE_DATAFILE=YES
DATA=<dataset to be loaded>
FORCE;               <-  is important because SAS is trying to insert a CHAR(40) into a field of length 19/22. The log

                                 will contain truncation warnings.
RUN;

or 

PROC SQL;
INSERT INTO OCLTGT.<tablename>(
BULKLOAD=YES
BL_DELETE_DATAFILE=YES
BL_DIRECT_PATH=NO
DBSASTYPE=(a='CHAR(19)'
b='CHAR(38)'                              <- Note this field is defined as numeric with length null in the DB, it can take upto

                                                        38 significant digits, so I used the length 38.

)

DBFORCE=yes
ERRLIMIT=1
%put &sqlxrc;
%put &sqlxmsg;
)
SELECT
..... <fields>
from
<dataset to be loaded>
;
QUIT;
RUN;

In both cases above, we use DBSASTYPE to tell SAS how to prepare the data for load. Here's the response I received from SAS Tech support regarding this -

"To create a table to database, you use DBTYPE option to assign a different data type. To read data into SAS, you use DBSASTYPE option to convert the data type to SAS type/format.

Proc sql insert and proc append load to an existing table, you do not need dbtype to change its data type. You need dbsastype to describe its data type to tell SAS how to prepare the data to insert/update the table in the database."

Hope this helps!

[SASKiwi]

Try this:

libname ora oracle user=myusr1 password=mypwd1 path=mypath;
data sasdata;
   set ora.catalina2( dbsastype= ( c1='char(20)') ) ;
run;

And the reference:

http://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#n1gv8bfdnhvy9yn1cj...

[Ksharp]

the max length of number sas can handle is 15 . So you'd better transform it into CHAR when moving data between them.

[PoornimaRavishankar]

Thank you!!! I believe as of now, that's the only way to go!