24 Apr

SAS: Numeric Functions

It’s late, and the book only list two numeric functions, so that’s all you get if you happen to read this.  It turns out, however, that ROUND was more than enough by itself.

ROUND

It does exactly what it sounds like, except, it doesn’t work like you’d expect.

data mydata;
	var = 123.456;
	round1 = round(var);
	round2 = round(var,2);
	round3 = round(var,-1);
run;

Obs      var      round1    round2    round3
 1     123.456      123       124        .

The first statement worked fine. It rounded, down, which is what you’d expect. The second statement, uh, it’s really hard to tell what it did. Finally, you can use a negative number, but, it will return a value of missing.

So, how do we set precision?  Awkwardly.

Here’s another example.

data mydata;
	var = 425.25;
	round1 = round(var,200);
run;

Obs      var     round1
 1     425.25      400

What happened here is that I set the increment value to 200. What that means is that SAS rounded to the nearest 200 increment. It searched like this.

0
200
400
600

Since 425.25 falls between 400 and 600, it rounded to the closest value, which was 400.

If I wanted to set a precision of 1 I have to do the following:

data mydata;
	var = 425.25;
	myround = round(var,.1);
run;

Obs      var     myround
 1     425.25     425.3

In other words, round to the nearest 0.1 increment, which in this case, is 0.3. This is totally, NOT, how I expected this to work.

I haven’t been doing this, mostly because I’m writing for me, but if I was explaining ROUND it would read like this:

ROUND(value, rounding increment);

I hope that helps.  It would be easy for most of us who have written code to screw this one up.

INT

INT actually does what it’s supposed to do. IT converts numeric data to an integer, like this:

data mydata;
	var = 425.25;
	myint = int(var);
run;

Obs      var     myint
 1     425.25     425
07 Mar

ROUND

ROUND works as follows,

ROUND(value, precision) – Rounds the value to the precision specified.

There is a minor difference between how MySQL and SQL Server use it,  it’s been bugging me, so I’m going to write about it.  Except for this one thing, the two functions work the same.  Here’s some code.

SELECT ROUND(123456.123456,5) AS Round5,
ROUND(123456.123456,4) AS Round4,
ROUND(123456.123456,3) AS Round3,
ROUND(123456.123456,2) AS Round2,
ROUND(123456.123456,1) AS Round1,
ROUND(123456.123456,0) AS Round0

Round5	        Round4	        Round3	        Round2	        Round1	        Round0
123456.123460	123456.123500	123456.123000	123456.120000	123456.100000	123456.000000

So far it should be pretty clear. It rounds to the precision. What happens when the value is .5? The answer is it rounds up.

SELECT ROUND(5.5,0);

Round0
5.5

It’s also possible to have a negative precision. For instance,

SELECT ROUND(123456.123456,-1) AS 'Round-1',
ROUND(123456.123456,-2) AS 'Round-2',
ROUND(123456.123456,-3) AS 'Round-3',
ROUND(123456.123456,-4) AS 'Round-4',
ROUND(123456.123456,-5) AS 'Round-5';

Round-1	        Round-2	        Round-3	        Round-4	        Round-5
123460.000000	123500.000000	123000.000000	120000.000000	100000.000000

As you can see a negative precision rounds to the left of the decimal point.

Now, the same queries in MySQL.

mysql> SELECT ROUND(123456.123456,5) AS Round5,
    -> ROUND(123456.123456,4) AS Round4,
    -> ROUND(123456.123456,3) AS Round3,
    -> ROUND(123456.123456,2) AS Round2,
    -> ROUND(123456.123456,1) AS Round1,
    -> ROUND(123456.123456,0) AS Round0;
+--------------+-------------+------------+-----------+----------+--------+
| Round5       | Round4      | Round3     | Round2    | Round1   | Round0 |
+--------------+-------------+------------+-----------+----------+--------+
| 123456.12346 | 123456.1235 | 123456.123 | 123456.12 | 123456.1 | 123456 |
+--------------+-------------+------------+-----------+----------+--------+
1 row in set (0.01 sec)

mysql> SELECT ROUND(5.5,0);
+--------------+
| ROUND(5.5,0) |
+--------------+
|            6 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(123456.123456,-1) AS 'Round-1',
    -> ROUND(123456.123456,-2) AS 'Round-2',
    -> ROUND(123456.123456,-3) AS 'Round-3',
    -> ROUND(123456.123456,-4) AS 'Round-4',
    -> ROUND(123456.123456,-5) AS 'Round-5';
+---------+---------+---------+---------+---------+
| Round-1 | Round-2 | Round-3 | Round-4 | Round-5 |
+---------+---------+---------+---------+---------+
|  123460 |  123500 |  123000 |  120000 |  100000 |
+---------+---------+---------+---------+---------+
1 row in set (0.00 sec)

See the difference? It removes the leading 0’s after the decimal point.

Would this matter to anyone? Not really. In PHP you would probably use number_format on the data regardless of how it arrived from the database, it just makes things nicer to look at in my examples if SQL Server rounded.

And how do you fix it? You would toss a CAST at it with a DECIMAL type, like this.

SELECT ROUND(123456.123456,3) AS 'Without Cast',
CAST(ROUND(123456.123456,3) AS DECIMAL(10,3)) AS 'With Cast'

Without Cast	With Cast
123456.123000	123456.123

One last difference between SQL Server and MySQL. SQL Server can truncate a value rather than round. Here’s an example.

SELECT ROUND(3.55,1,0) AS 'Same As Round',
ROUND(3.55,1) AS 'Regular Round',
ROUND(3.55,1,1) AS 'Truncated'

Same As Round	Regular Round	Truncated
3.60	        3.60	        3.50

One thumbs up for this because MySQL can’t do this in the ROUND function.

Finally, ROUND can be erratic in some places,

SELECT ROUND(3.9995,3) AS Erratic;

Erratic
4.0000

Well, it can happen, so just be aware of it if you need heavy duty precision some day and things aren’t quite adding up.