BookmarkSubscribeRSS Feed
Jay_TxOAG
Quartz | Level 8

In the below code the 1st 4 values, read in, equal the 5th value, but after conversion from character to numeric the comparision statement is always false. I have read several sites via Google, but have been unable to determine what I am doing wrong. If I put a ROUND function on the SUM statement (to .00001), the values are considered equal. I would like to use the values in subsequent processing and not have to use the ROUND function to check my computations. I am really interested in understandning this problem, thanks for your help.

 

data HAVE;
Format a a_num b b_num c c_num d d_num TOTAL TOTAL_num;
Length a b c d TOTAL $100;
infile datalines delimiter="~" dsd;
input a $ b $ c $ d $ TOTAL $;

a_num=input(a,comma18.);
b_num=input(b,comma18.);
c_num=input(c,comma18.);
d_num=input(d,comma18.);
TOTAL_num=input(TOTAL,comma18.);

datalines;
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
;

data WANT;
Format CHECK;
Set HAVE;

SUM_TOTAL=sum(a_num,b_num,c_num,d_num);
If sum_total^=TOTAL_num then CHECK="X";
Format a_num b_num c_num d_num TOTAL_num SUM_TOTAL comma18.2;
Run;

 

 

3 REPLIES 3
art297
Opal | Level 21

Welcome to the world of numeric precision. For a detail explanation take a look at: http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p0ji1unv6thm0dn1gp4t...

 

Bottom line, use the round function.

 

Art, CEO, AnalystFinder.com

 

mkeintz
PROC Star

And just to add to @art297's comment, this is NOT a sas problem.  Any computer language that treats the values you input as "double-precision" (i.e. most languages) would have the same behavior.

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
rogerjdeangelis
Barite | Level 11
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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1032 views
  • 0 likes
  • 4 in conversation