numeric variable greater than 1E-17

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

numeric variable greater than 1E-17


Hi,

I'm reading a table with to access/teradata with sas9.1.3.

This table has a numeric variable n_pratica (primary key) format/informat 19. but when i read this variable, sas rounds and converts wrong.

For example i have two clients with n_pratica:

1) client=1  n_pratica=300200600000000128

2)client=2 n_pratica=300200600000000160.

If i read these observations trough teradata clients i read them exactly, if i read these observations trough sas with libname access/teradata these observations are reading in this view:

1) client=1  n_pratica=300200600000000128

2)client=2 n_pratica=300200600000000128.

I tried to drop format 19. and then i tried to convert in char and the result is:

1) client=1  n_pratica=300200600000000000

2)client=2 n_pratica=300200600000000000

beacuse sas rounds number>16.

I tried only convert in char with put function  but i read this:

1) client=1  n_pratica=300200600000000128

2)client=2 n_pratica=300200600000000128.

How do I do for read correctly this variable?

Thank you!


Accepted Solutions
Solution
‎03-19-2013 08:02 AM
Super User
Posts: 5,257

Re: numeric variable greater than 1E-17

What platform are you on?

For Windows, the largest integer that can be stored with correct precision is 9,007,199,254,740,992.

Depending on how you wish to use this within SAS, but converting to char seems to be the easiest way. Take a look at the DBSASTYPE= ds option.

But could consider try to not to move this columns to SAS, explicit/implicit pass-thru shouldn't suffer from this problem.

Data never sleeps

View solution in original post


All Replies
Solution
‎03-19-2013 08:02 AM
Super User
Posts: 5,257

Re: numeric variable greater than 1E-17

What platform are you on?

For Windows, the largest integer that can be stored with correct precision is 9,007,199,254,740,992.

Depending on how you wish to use this within SAS, but converting to char seems to be the easiest way. Take a look at the DBSASTYPE= ds option.

But could consider try to not to move this columns to SAS, explicit/implicit pass-thru shouldn't suffer from this problem.

Data never sleeps
New Contributor
Posts: 3

Re: numeric variable greater than 1E-17

Thank you for your answer.

My platform is unix AIX 5.3.

I tried to convert variable in char with this code

libname RATING teradata user=xxxxxxx password=xxxxxxxxxx

  database=DXDWH_STG tdpid=tdp cast=no;

prova (keep=n_pratica  time_anag  cope nuovo1 );

RATING.BT_STRATEGY_ANAG;

where  cope in ('09640362', '39395773');                 

nuovo1=put( n_pratica , 18.);      

  run;

but SAS converts the value as read it.

I tried to use DBSASTYPE but i don't resolve anything.

print data=RATING_N.BT_STRATEGY_ANAG (keep=n_pratica cope time_anag DBSASTYPE=(n_pratica='char(19)')) ;

cope in ('09640362', '39395773');

;

The result is the same.

Super User
Posts: 5,257

Re: numeric variable greater than 1E-17

It seems that the code you are pasting is not complete.

I guess that the first is a data step - and that does not work, since these assignment statements executes within SAS, with the values already transformed into SAS numeric format.

By transforming the step to SQL, it might send the assignment down to Teradata via implicit pass thru. Not sure about what degree of implicit pass thru it is in 9.1.3 (upgrade!).

Attempt 2 should work (at least if you add the where statement to line 2 Smiley Wink ). I have no means of testing this at my site. But still, why do you want to print those numbers?

Data never sleeps
Super Contributor
Posts: 578

Re: numeric variable greater than 1E-17

You might try pass-thru queries.  I'm not familiar with teradata syntax, but this might work.

proc sql noprint;

connect to teradata as Rating (user=xxxxxxx password=xxxxxxxxxx  database=DXDWH_STG tdpid=tdp cast=no);

create table want as select * from connection to rating (

     select n_pratica, time_anag, cope, cast(n_pratice as char(20)) nuovo1 from BT_STRATEGY_ANAG where cope in ('09640362', '39395773')

);

disconnect from rating;

quit;

New Contributor
Posts: 3

Re: numeric variable greater than 1E-17

Thank you!!! I've resolved! Smiley Happy

Super Contributor
Posts: 644

Re: numeric variable greater than 1E-17

This is not a specific SAS limitation except that SAS stores all numbers as floating point:  SAS uses the operating system's (up to) 8 byte floating point representation.  You will find the same limitations in MS Excel, only worse because any digits after the first 15 are truncated to zero.

The data you show looks like it is an ID rather than a quantity, in which case if you have to import it into SAS follow LinusH's advice and convert to character.

Richard

Super User
Super User
Posts: 6,502

Re: numeric variable greater than 1E-17

You need to talk to your Teradata programmer.  They should either convert the variable n_pratica from DECIMAL(19) to CHAR(19) or VARCHAR(19).  They could possibly create a view so that they do not need to rebuild the database. 

Then you will not have any trouble reading those id's from TD into SAS.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 381 views
  • 4 likes
  • 5 in conversation