A test program to demonstrate the advantage of storing values in low units without decimals

In big Data Warehouses with very large tables the results of summarizing variables with decimals are often not exact. The reason is the internal representation of floating point decimals (reals) and the difference varies between different platforms. The test program shows the difference between VALUE (figures with decimals like DM) and VALCORR (the same figures multiplied by 100 so hat the decimals disappear).

%* To summarize 10 million invoices with an amount of 1000.02 ;
%* must not be a problem for a SAS software application !!    ;

%* This example demonstrates the advantage of storing values  ;
%* in the smallest currency as integer figures without        ;
%* decimals                                                   ;

%* (c) Wilfried Schollenberger                                ;
%*     WS Unternehmensberatung und Controlling-Systeme GmbH   ;
%*     Bergstrasse 7                                          ;
%*     D-69120 Heidelberg                                     ;
%*     EMail: wisch@attglobal.net                                   ;

%LET value= 1000.02;    * value of a singl observation    ;
%LET count= 1000000;    * number of observations per group;
%LET groups= 10;        * number of groups                ;

PROC format ;           * format to display Pfennig as DM ;
 picture dm  (round)
 -9999999999999.99 -< 0
     = "0.000.000.000.009,00" (mult = 1 PREFIX="-")
 0 - 99999999999999.99
     = "00.000.000.000.009,00" (mult = 1)
 ;

* create the data by a view to save disk space ;
DATA work.test  /VIEW=work.test;
 value = &value;
 valcorr = round(&value * 100,1);
 do group = 1 to &groups;
  PUT group =;
  do i = 1 to &count;
     OUTPUT;
  end;
 end;
RUN;

* Proc Summary would produce the same results  ;
PROC tabulate data=work.test;
  class group;
  var value valcorr;
  table group all,
         value  = "stored with decimals"   *sum=" "  * F=commax21.2
         valcorr= "stored as ""Pfennige""" * sum=" " * F=DM.
         N*F=COMMAX12.
       /rts=7;
RUN;


WS Unternehmensberatung und Controlling-Systeme GmbH
Bergstraße 7
D-69120 Heidelberg

Tel.: +49 6221 401 409
Fax: +49 6221 401 422
EMail: wisch@attglobal.net