topic Re: Problem with importing data in SAS Procedures

Problem with importing data

deleted_user — Wed, 26 Mar 2008 19:15:24 GMT

I have a macro that imports data from SQL using ODBC

proc sql noprint;
DROP TABLE tb_contas;
create table tb_contas
select * from SQLX.tb_contas;
quit;

this works well but I found a problem with big values

SQL table tb_contas there is a column x decimal(19.0)
on column x there is a value 844306657506657506

but when a search for that value on SAS tb_contas the result is the value 844306657506657536

I've been tried to create a table before that with format statemet (format=20.)
and execute a insert but the result is the same..

anybody know what is happen??

thanks

Vander

Re: Problem with importing data

Doc_Duke — Thu, 27 Mar 2008 19:55:10 GMT

Vander,

You've got a problem with round-off errors. SAS stores numbers as real numbers, so there is a maximum integer size that it can exactly represent.

The maximum varies by platform, so you will need to go to the SAS documentation for your platform to look it up.

Doc

Re: Problem with importing data

deleted_user — Fri, 28 Mar 2008 12:55:18 GMT

such wide integers need to be converted, either to character strings of appropriate length, or into two numeric variables. I expect the conversion needs to be completed on the data base platform, before ODBC tries to transfer the values.

Re: Problem with importing data

deleted_user — Fri, 28 Mar 2008 13:20:10 GMT

Thank for the answers

Re: Problem with importing data

deleted_user — Sat, 29 Mar 2008 03:32:58 GMT

I would second this, we have noticed that the same large integer number coming from two different ODBC sources; DB2 and SQL Server get scrambled up differently.

Highly suggest doing as Peter mentioned and convert at the base platform first!

Ike Eisenhauer Message was edited by: WDEisenhauer