SAS - PROC SQL - Case When arithmetic not making sense

Question

In SAS Proc SQL, I am using this Case statement to try to mark policies that have differences other than .015 per year:

CASE WHEN (MOD1 - MOD0) NOT = .015 THEN 2
     WHEN (MOD2 - MOD1) NOT = .015 THEN 3
     WHEN (MOD3 - MOD2) NOT = .015 THEN 4
     WHEN (MOD4 - MOD3) NOT = .015 THEN 5
     ELSE 0 END

The weird thing is it doesn't work when it feels like it should. ( e.g. mod1 = .955 and mod0 = .94 but the first statement believes its not .015) At first I believed it to be an issue with digits but that never worked.

Oddly enough this does work and I don't know why.

CASE WHEN (MOD1 - MOD0) NOT = (1- .985) THEN 2
     WHEN (MOD2 - MOD1) NOT = (1- .985) THEN 3
     WHEN (MOD3 - MOD2) NOT = (1- .985) THEN 4
     WHEN (MOD4 - MOD3) NOT = (1- .985) THEN 5
     ELSE 0 END

But this doesn't work

CASE WHEN (MOD1 - MOD0) NOT = (.2- .185) THEN 2
     WHEN (MOD2 - MOD1) NOT = (.2- .185) THEN 3
     WHEN (MOD3 - MOD2) NOT = (.2- .185) THEN 4
     WHEN (MOD4 - MOD3) NOT = (.2- .185) THEN 5
     ELSE 0 END

I have workarounds to get around any of these (including using data steps) so I'm not really looking for solutions to the issue but more answers as to what is going on here. I like to understand why things don't work rather than just knowing what I can and can't do.

Thanks!

Assuming floats, dup of https://stackoverflow.com/questions/588004/is-floating-point-math-broken — Salman A, Oct 30 '19 at 11:10
And the solution is to use DECIMAL datatype to store monetary information. — Salman A, Oct 30 '19 at 11:30

score 0 · Answer 1 · answered Oct 30 '19 at 11:09

0

The problem is probably floating point arithmetic. Exact comparisons to floats is troublesome. Try something like this:

CASE WHEN ABS((MOD1 - MOD0) - 0.015) > 0.00001 THEN 2
     WHEN ABS((MOD2 - MOD1) - 0.015) > 0.00001 THEN 3
     WHEN ABS((MOD3 - MOD2) - 0.015) > 0.00001 THEN 4
     WHEN ABS((MOD4 - MOD3) - 0.015) > 0.00001 THEN 5
     ELSE 0
END

The 0.00001 is arbitrary. It is just some small number.

answered Oct 30 '19 at 11:09

Gordon Linoff

1,242,037
58
646
786

Ah. Okay. I should have thought about floating point. Thanks for the help! – Yugy Oct 30 '19 at 12:29

Llex · Answer 2 · 2019-10-30T11:57:12.093

0

You can use round function:

CASE WHEN round(MOD1 - MOD0,.0001) NOT = .015 THEN 2
     WHEN round(MOD2- MOD1,.0001) NOT = .015 THEN 3 
     WHEN round(MOD3 - MOD2,.0001) NOT = .015 THEN 4
     WHEN round(MOD4 - MOD3,.0001) NOT = .015 THEN 5
     ELSE 0 END

edited Oct 30 '19 at 11:57

answered Oct 30 '19 at 11:32

Llex

1,770
1
12
27

SAS - PROC SQL - Case When arithmetic not making sense

2 Answers2