topic Have inserted numeric column to SQL table. Why is the value rounded? in SAS Programming

Have inserted numeric column to SQL table. Why is the value rounded?

Mayt — Sun, 26 Nov 2023 14:06:41 GMT

Hi guys!

I've tried to insert numeric value to column in sql server. I just want it to have exact value as the source column, but I don't know why the value in destination column is rounded.

the source column is A.SubContractNo. The value is 510000029700000.

the destination column value is 510000030000000.

I expected it to have 510000029700000 as the source.

The destination column is float type.

/* my code does left join and then insert to the desination table*/
proc sql;
	insert into sqllib.output_tbl
	select A.ValueDate
	, A.RegisNo 
	, A.ContractNo
	, A.SubContractNo
	, A.ValueDate
	, B.Detail
	from work.input_tbl as A
	left join ssreout.detail_tbl as B
	on A.RegisNo = B.RegisNo
	and A.ValueDate = B.ValueDate
	where B.RegisNo is not null
	and B.ValueDate is not null;
run;

Re: Have inserted numeric column to SQL table. Why is the value rounded?

PaigeMiller — Sun, 26 Nov 2023 14:29:03 GMT

SAS has trouble handling some 15-digit and above integers. Best to handle this as a character string, then no problems should result.

Re: Have inserted numeric column to SQL table. Why is the value rounded?

mkeintz — Mon, 27 Nov 2023 06:12:26 GMT

What is the storage length of subcontractno in the destination dataset (sqllib.output_tbl)? And in the source dataset (work.input_tbl)? Run a PROC CONTENTS against the dataset(s) to see what SAS thinks the storage length is.

If subcontractno (as a floating point numeric value) is stored with a length of 6 or less, then there will be numeric precision problems for the value of 510000029700000. On my windows machine, a length of 6 stores 510000029700000 as 510000029999104. And I believe this will be the case with unix machines as well.

But since that is not the same as 510000030000000 that you report, this may not be just a numeric precision issue (if it is a precision issue at all).

BTW, you can run this test to see what various length attributes do when storing the value 510000029700000.

data t;
  li=constant('exactint');
  length x8 8 x7 7  x6 6  x5 5  x4 4;
  array _x x: ;
  do over _x;
    _x=510000029700000;
  end;
run;
data _null_;
  set t;
  put li=16.0   ' Maximum consecutive exact integer ';
  array _x x: ;
  do over _x;
    put _x=z16.0 ;
  end;
run;

which produces these values on my sas log:

li=9007199254740992  Maximum consecutive exact integer
x8=0510000029700000
x7=0510000029700000
x6=0510000029696000
x5=0510000028975104
x4=0509999985983488

In the end, this is a good reason to store ID values as character variables. Which can cause other annoyances - but that is for another discussion.