topic Re: Variable appears to have numbers to right of decimal in SAS Programming

Variable appears to have numbers to right of decimal

tedway — Wed, 17 Apr 2024 15:01:24 GMT

I have a dataset with codes that look like this:

rcode
1
2
3
4
5
6
7
8
9

When I try to filter on code 7, it doesn't return any results.

This code, for example:

proc sql;
select * from ds
where rcode in (1,2,3,4,5,6,7,8,9)
;quit;

Returns this (note that 7 is missing):

rcode
1
2
3
4
5
6
8
9

It looks like maybe the 7 code has digits to the right of the decimal.

data ds2;
set ds;
test = put (rcode,bestd32.);
run;
proc print data=ds2 NOOBS; run;

Results of the above code:

rcode	test
1	1
2	2
3	3
4	4
5	5
6	6
7	7.0000000000000000
8	8
9	9

If the code doesn't equal 7, then what is it's value? I'm not looking for workarounds but rather a way to see the true value (maybe it's 7.0000000000001, for example). Once I know what the true value is, I can have the person that maintains this data correct it. Right now they are saying it equals 7.

I've also tried viewing the dataset in EG but it just looks like a 7.

Filtering on 7 produces no results (not an issue with any of the other numbers):

Re: Variable appears to have numbers to right of decimal

ballardw — Wed, 17 Apr 2024 15:22:25 GMT

Format.

Something to remember when dealing with SAS data is variables always have a format assigned. The format may round or truncated displayed characters.

So if I execute this code:

data junk;
   x= 7.0000000000056789;
run;

and open the table viewer I see a value of 7. Why? Because the default format that was assigned is BEST12. since I did not explicitly assign one. The digits other that 0 are past what 12 characters would display so the best format rounds to the integer.

However if assign a format such as F32.20 (32 characters allowing for 20 decimal places) then I see 7.00000000000568000000 (more zeroes to the right to make up 20).

So change your format to display more decimal places. I would start with 16 and not use one of the BEST. If you have values in the 14/15th decimal place they may not be accurate due to precision of storage so what you see may not be what the source has though likely something.

You may have to point to the data file actually read. Since this seems like a surprise I might guess this is from connecting to an external data source and not reading a text file. So properties of the source may also be masking such extraneous digits.

Re: Variable appears to have numbers to right of decimal

FreelanceReinh — Wed, 17 Apr 2024 15:28:58 GMT

Hello @tedway,

There are a few numeric formats which reveal the actual (binary!) content of a numeric variable. These include BINARY64. (binary format, i.e., sequence of 0s and 1s), HEX16. (same, but in hexadecimal digits) and RB8. (character string, more difficult to use). But I think you are more interested in the decimal value. In this case you create a new variable, say, DIFF7, defined as

diff7=rcode-7

In the observation where you see rcode=7 it will be something like 8.881784E-16 or maybe a negative value of similar order of magnitude. Then you know that rcode really contains 7 plus that tiny (positive or negative) value.

Re: Variable appears to have numbers to right of decimal

FreelanceReinh — Wed, 17 Apr 2024 15:56:48 GMT

@ballardw wrote:

Something to remember when dealing with SAS data is variables always have a format assigned. The format may round or truncated displayed characters.

@ballardw: Exactly. But sadly this is true for F32.20, BEST32., E32., etc. as well. So there are cases where these (non-binary) formats still hide those tiny differences. Then it may help to set

options decimalconv=stdieee;

but personally I prefer the HEX16. format and use the BASECONVFP function if an exact decimal value is of interest.