PRB: CALCULATE STD--Standard Deviation--Gives Numeric Overflow (172084)

   * Call this program by ?std("TableName","FieldName") or
   * by assigning the result to a variable, y= std("TableName","FieldName")
   *
      PARAMETER mtable mfield
      SELECT SUM(&mfield^2) FROM (mtable) INTO ARRAY yy
      SELECT SUM(&mfield)^2 FROM (mtable) INTO ARRAY zz
      SELECT COUNT(&mfield) FROM (mtable) INTO ARRAY xx
      stdev=((xx*yy-zz)/xx^2)^.5
      RETURN ROUND(stdev,12)
				

   ?std("values","num")
				

MORE INFORMATION

The numeric overflow is generally observed when a table contains the same repeated real values over a broad range of records. For example, if a table contains 10 records and each record's value is 0.031, the CALCULATE STD() will produce an overflow. This can vary depending on the value and the number of records.

One way to detect that there was a numerical overflow is to display the result of the CALCULATE STD() to the desktop or in a WAIT WINDOW. If SET TALK is ON, the result will be displayed on the desktop automatically. If there is an overflow, all asterisks will be displayed. Also, if the result of the CALCULATE STD() is appended or replaced to a numerical field in a table, the following error may occur:

Steps to Reproduce Behavior

Enter the following code in a program and run it:

   CLEAR
   SET SAFETY OFF
   SET DECIMALS TO 12
   CREATE TABLE stdresult (num N(15,8)) &&Table for result of calculation
   APPEND BLANK
   CREATE TABLE stdtest (num N(15,8))   &&Table of values for calculation
   FOR i = 1 TO 10
      INSERT INTO stdtest(num) VALUES (0.031)
   ENDFOR

   FOR i = 1 TO RECCOUNT()
      ?ALLTRIM(STR(i))+" "
      SET TALK OFF
      CALCULATE STD(num) FOR RECNO()<i TO nresult && Comment this line &
                                                  && the next line once
                                                  && you uncomment the
                                                  && commented lines below.

      ??nresult

      *nresult=custstd('stdresult','num') && Uncomment this line & the next
                                          && line to compute & display the
                                          && standard deviation with a
                                          && user-defined function.

      *?nresult
      SELECT stdresult
      REPLACE num WITH nresult   &&Overflow error on 10th iteration
      SELECT stdtest
   ENDFOR

   SET SAFETY ON
   SET TALK ON
   SET DECIMALS TO 2

   PROCEDURE custstd
   PARAMETER mtable mfield
   SELECT SUM(&mfield^2) FROM (mtable) INTO ARRAY yy
   SELECT SUM(&mfield)^2 FROM (mtable) INTO ARRAY zz
   SELECT COUNT(&mfield) FROM (mtable) INTO ARRAY xx
   stdev=((xx*yy-zz)/xx^2)^.5
   RETURN ROUND(stdev,12)
				

After running this code, the first nine iterations should be all zeroes. The calculation will be all asterisks and will produce the overflow error message on the REPLACE command.