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;
SAS has trouble handling some 15-digit and above integers. Best to handle this as a character string, then no problems should result.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.