Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- proc sql sum is inaccurate

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 02-06-2015 01:09 PM
(5334 views)

I have data like this:

Pat_ID Discharge Admit Paid_Amount

A 2/1/2012 -20.48

A 2/1/2012 -10.18

A 2/1/2012 -1300.44

A 2/1/2012 1300.44

A 2/1/2012 10.18

A 2/1/2012 20.48

A 1/1/2012 2/1/2012 50.50

A 1/1/2012 2/1/2012 60.48

I need to sum up the paid_amounts by Pat_ID, discharge and admit so I used this:

proc **sql**;

create table sumPaid as

select pat_id, admit, discharge, sum(paid_amount) as paid_amount

from pat_det

by pat_id, admit, discharge;

quit;

Which gives me something like this:

pat_ID Admit Discharge paid_amount

A 2/1/2012 4.54747474E-13

A 2/1/2012 1/1/2012 110.98

You can tell that the observations with blank discharge but admit 2/1/2012 have equal paid_amounts that should add up to 0 yet for similar situations, I get extremely small E-13 sums and I have no idea why that could be.

9 REPLIES 9

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

One possibility is that you are tricked by the format. See below:

**data** pat_det;

input Pat_ID :$10. (Discharge Admit) (:mmddyy10.) Paid_Amount /*default format is best12.*/;

cards;

A . 2/1/2012 -20.48

A . 2/1/2012 -10.18

A . 2/1/2012 -1300.44

A . 2/1/2012 1300.4400000001

A . 2/1/2012 10.18

A . 2/1/2012 20.48

A 1/1/2012 2/1/2012 50.50

A 1/1/2012 2/1/2012 60.48

;

**proc** **sql**;

create table sumPaid as

select pat_id, admit, discharge, sum(paid_amount) as paid_amount

from pat_det

group by pat_id, admit, discharge;

**quit**;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

One way to reconcile is to use ROUND(), before SUM.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

After investigating, it seems that the issue is caused by sql which stores numbers as floating numbers and supposedly, I have to specify the sum as numeric(8,2) with a cast function. I'm not sure how to do that, so instead of using proc sql I used proc summary and the issue went away.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Good that you got the answer you needed, but your explanation is off.

PROC SUMMARY worked because by default it applies the format from the source variable to the derived statistic. So if you had 8.2 format attached to your PAID_AMOUNT variable then the SUM statistic would also be displayed using the 8.2 format. In SQL syntax you just need to add the FORMAT option to your derived variable to see the same results.

select pat_id, admit, discharge, sum(paid_amount) as paid_amount format=8.2

SAS stores numbers as floating point. There is no way to represent decimal values (like dollars and cents) exact using binary floating point numbers. Rounding in advance won't really help. Rounding after summing can help a little. If you really want to work with dollars and cents then you might consider storing the values as # of CENTS instead of # of DOLLARS because then they will be store as integers and the inability of binary floating point format to exactly represent decimal fractions will not apply.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

An exhaustive explanation about floating point numbers in SAS (and in software in general):

http://blogs.sas.com/content/sasdummy/2012/03/01/precision-in-sas-numbers/

There you will learn more than you ever wanted to know!

Chris

Check out SAS Innovate on-demand content! Watch the main stage sessions, keynotes, and over 20 technical breakout sessions!

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.