Making dollars and cents total exactly
inspired by
https://goo.gl/QijwIC
https://communities.sas.com/t5/Base-SAS-Programming/Unable-to-get-correct-precision-in-conversion-from-character-to/m-p/341321
Great question and very nice sample data. Thanks!!!
When working with dollars and cents and totals less than the national debt.
You can convert your input to numbers that have an exact representation
in a binay float. All you sums will be exact.
HAVE
====
Up to 40 obs WORK.HAVE total obs=3
Obs A B C D TOTAL
1 1,567,378.77 953,466.02 50,949.03 532.82 2,572,326.64
2 15,884,327.48 7,330,140.68 559,032.01 1,743.28 23,775,243.45
3 165,119.70 44,705.58 209,825.28
WANT (Sum total to be exactly = to sum(a,b,c,d)
Obs A B C D TOTAL SUM_TOTAL
1 1,567,378.77 953,466.02 50,949.03 532.82 2,572,326.64 = 2572326.64
2 15,884,327.48 7,330,140.68 559,032.01 1,743.28 23,775,243.45 = 23775243.45
3 165,119.70 44,705.58 209,825.28 = 209825.28
WORKING CODE
============
a_num=100*input(a,comma18.);
b_num=100*input(b,comma18.);
c_num=100*input(c,comma18.);
d_num=100*input(d,comma18.);
TOTAL_num=100*input(TOTAL,comma18.);
FULL SOLUTION
=============
data HAVE;
Length a b c d TOTAL $100;
infile datalines delimiter="~" dsd;
input a $ b $ c $ d $ TOTAL $;
a_num=100*input(a,comma18.);
b_num=100*input(b,comma18.);
c_num=100*input(c,comma18.);
d_num=100*input(d,comma18.);
TOTAL_num=100*input(TOTAL,comma18.);
keep
a_num
b_num
c_num
d_num
total
TOTAL_num;
cards4;
1,567,378.77~953,466.02~50,949.03~532.82~2,572,326.64
15,884,327.48~7,330,140.68~559,032.01~1,743.28~23,775,243.45
165,119.70~44,705.58~~~209,825.28
;;;;
run;quit;
data WANT;
Set HAVE;
SUM_TOTAL=sum(a_num,b_num,c_num,d_num)/100;
If sum_total^=TOTAL_num then CHECK="X";
Format a_num b_num c_num d_num TOTAL_num SUM_TOTAL comma18.2;
Run;
... View more
