Jedi SAS Tricks - The Double vs. Decimal Dilemma

4

I ran across an interesting conundrum the other day. The intent was to compare the value of X to a list of desired values, and if X matched one of the values in the list, set Flag to 1, otherwise set Flag to 0.  I wrote and executed this test program, then reviewed the results. Oddly, only observations 1 and 9 had Flag set to 1. However, I could clearly see that the values of X in observations 3,5,and 7 should also have had Flag set to 1, because those values of X were definitely on my list!

data test;
   do id=-0.2 to 0.2 by .05;
      x=id;
      if x in (-0.2 -0.1 0 0.1 0.2) then flag=1;
      else flag=0;
      output;
   end;
run;
proc print data=test;
run;

Result from the first try

What dark force could be thwarting my fairly straightforward computation? Master Yoda once said "In a dark place we find ourselves, and a little more knowledge lights our way." So off to the SAS Online Docs I went to find enlightenment. In the end, it was just that age-old computer problem of representing base ten numeric values exactly when working with double-precision (8 byte) floating point numbers. Most computer systems use floating point numeric representation because numbers of widely varying magnitude can be represented - numbers from ≈ 1.7976931348623157x10308 to ≈ 2.2250738585072014x10−308. But computers actually store and process binary or hexadecimal values, not base ten. And the scheme for representing floating point numbers with only 8 bytes means that many base ten numbers can't be represented exactly in binary or hex. So a floating point number is actually a signed approximation of the value specified which can store large magnitude numbers with limited precision. There's a lot of great background information in the online docs Numerical Accuracy in SAS Software article. The section on Numeric Comparison Considerations gets right to the heart of our problem. The docs suggest using rounding during comparison, and a quick test of that technique produced the correct results:

data test2;
   do X=-0.2 to 0.2 by .05;
      if round(x,0.01) in (-0.20 -0.10 0.00 0.10 0.20) then flag=1;
      else Flag=0;
      output;
   end;
run;
proc print data=test2;
run;

Result after rounding X

But wouldn't it be awesome if we could process exactly the value specified in our programs? Most databases support storing numeric values as exact, fixed-decimal instead of floating point without limiting size to 8 bytes in a data type named DECIMAL. In Chapter 3 of my book "Mastering the SAS® DS2 Procedure”, we discuss the fact that, while you can't store DECIMAL data in a SAS data set, you can process DECIMAL data in base SAS using DS2. Of course, because DECIMAL values store exact numbers and allows a maximum of 52 digits of precision, eventually we'll hit a magnitude limit. But in this case, 52 digits of precision is more than sufficient to meet our needs our needs. So this DS2 program provides yet another solution to our dilemma, and one that does not require rounding:

proc ds2;
data test3/overwrite=yes;
   dcl decimal (5,2) X;
   dcl int Flag;
   method run();
   	do X=-0.2 to 0.2 by .05;
   	   if x in (-0.20 -0.10 0.00 0.10 0.20) then flag=1;
         else Flag=0;
   	   output;
   	end;
	end;
enddata;
run;
quit;
proc print data=test3;
run;

DS2 DECIMAL Result

So there you have it, the DOUBLE vs. DECIMAL dilemma deciphered. You can download the code from this post here.
Until the next time, may the SAS be with you!
Mark

Tags
Share

About Author

SAS Jedi

Principal Technical Training Consultant

Mark Jordan (a.k.a. SAS Jedi) grew up in northeast Brazil as the son of Baptist missionaries. He served 20 years as a US Navy submariner, pursuing his passion for programming as a hobby. Upon retiring from the Navy in 1994, he turned his hobby into a dream job and has been a SAS programmer ever since. Mark writes and teaches a broad spectrum of SAS Foundation programming classes, and is proud to announce his first book, "Mastering the SAS® DS2 Procedure: Advanced Data Wrangling Techniques". When he isn’t writing, teaching, or posting “Jedi SAS Tricks” here on the SAS Learning Post, Mark enjoys playing with his grand and great-grandchildren, hanging out at the beach and reading science fiction novels. His secret obsession is flying toys – kites, rockets, drones – and though he usually tries to convince Lori that they are for the grandkids, she isn't buying it. Mark lives in Toano, VA with his wife, Lori, and their cat, the amazing Tiger Man. To connect with Mark, check out his SAS Press Author page, follow him on Twitter @SASJedi or connect on Facebook or LinkedIn.

4 Comments

  1. Rick Wicklin

    Thanks for the timelyreminder and the clever workaround. Nice application of the 'decimal' declaration. Incidentally, your example has a name in the numerical analysis community! It is known as as "10.0 times 0.1 is hardly ever 1.0" (due to Kernighan and Plauger, 1974, The Elements of Programming Style). For SAS customers who are not using DS2, the advice to heed is "Avoid testing floating-point values for equality." An example and SAS statementsare discussed on my blog, where I show how to make fuzzy comparisons (similar to your use of the ROUND function).

Leave A Reply

Back to Top