DATA Step, Macro, Functions and more

Round numbers sum

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

Round numbers sum

I have a code as below

proc sql;

select spriden_id ,tbraccd_detail_code,sum (tbraccd_AMOUNT) as Amount_Due

from stg.tbraccd a, stg.spriden b

where

 b.spriden_change_ind is null

group by spriden_id, tbraccd_detail_code

HAVING SUM(TBRACCD_AMOUNT) <0;

quit;

 

But my output amount_due is like -711e-16, -284E-16, etc

How can i correct the amount_due's output


Accepted Solutions
Solution
‎10-06-2015 10:21 AM
Super User
Super User
Posts: 7,942

Re: Round numbers sum

Sorry, who is this in response to?  Also, what software are you using, it seem Enterprise Guide, but posted in SAS Base?  For the error, it looks like some special characters were taken over, maybe you copied and pasted from somewhere, I would recommend to paste it into Notepad, then copy from there into whatever you are using and try again.

View solution in original post


All Replies
Regular Contributor
Posts: 212

Re: Round numbers sum

proc sql;
select spriden_id ,tbraccd_detail_code,sum (tbraccd_AMOUNT) as Amount_Due format=best20.
from stg.tbraccd a, stg.spriden b
where
 b.spriden_change_ind is null
group by spriden_id, tbraccd_detail_code
HAVING SUM(TBRACCD_AMOUNT) <0;
quit;

Put the format=best20. option it will solve.

 

Att

Contributor
Posts: 47

Re: Round numbers sum

Posted in reply to DartibaliRodrigo

When i do format=best20. it gives me

 -7.10542865412511E-14

Another solution?

Regular Contributor
Posts: 212

Re: Round numbers sum

Then use the round() function wthin the sum()

Valued Guide
Posts: 860

Re: Round numbers sum

[ Edited ]

Round() is a sas function:

 

data have;
one = 1.0000000001;
two = 1.0000000002;
run;

data want;
set have;
want = round(sum(one,two));
run;

Super User
Super User
Posts: 7,942

Re: Round numbers sum

As the SQL you are using is running through the SAS compiler (its not a pass through to a database) you can quite happily use any SAS function within the SQL, so round() is perfectly acceptable:

proc sql;
  select  SPRIDEN_ID,TBRACCD_DETAIL_CODE,round(sum(TBRACCD_AMOUNT),0.1) as AMOUNT_DUE
  from    STG.TBRACCD A, STG.SPRIDEN B
  where   B.SPRIDEN_CHANGE_IND is null
  group by SPRIDEN_ID,TBRACCD_DETAIL_CODE
  having  sum(TBRACCD_AMOUNT) <0;
quit;
Regular Contributor
Posts: 212

Re: Round numbers sum

If you need to "ROUND" your function or number then use the round() function, but if you want to show the numbers without the exponentials then use the format= option after you rename the column.

 

Att

Contributor
Posts: 47

Re: Round numbers sum

Posted in reply to DartibaliRodrigo

I don't have to do round. I just want to see a nice number. What format should i use

Regular Contributor
Posts: 212

Re: Round numbers sum

I think there is no way to format this huge number you got there.

 

So i think the best one would be the best20. just like this -4.686859964978E-58

Super User
Super User
Posts: 7,942

Re: Round numbers sum

Posted in reply to DartibaliRodrigo

I think this has been discussed in a few threads.  Using the format= option is inherently hiding part of a data operation.  To my mind it is better to explicitly state what you are doing to a data item in code - rather than letting SAS do it behind the scenes.  For example, in your scenario, unless one goes through the code, and knows to remove that format, the value may "change" from an output perspective without being picked up, but as soo as you see a round (or one of the other options) you can tell that something is being doen to the data element.

Contributor
Posts: 47

Re: Round numbers sum

It gave me this error in the atachment,


error.PNG
Solution
‎10-06-2015 10:21 AM
Super User
Super User
Posts: 7,942

Re: Round numbers sum

Sorry, who is this in response to?  Also, what software are you using, it seem Enterprise Guide, but posted in SAS Base?  For the error, it looks like some special characters were taken over, maybe you copied and pasted from somewhere, I would recommend to paste it into Notepad, then copy from there into whatever you are using and try again.

Regular Contributor
Posts: 212

Re: Round numbers sum

This error msg appear mostly when you copy a whole cell from excel to a SAS Section in a Column name.

 

Need to figure it out witch column has this special character in it "°".

 

Copy the column names to a Word document and press the special characters button to see witch column has this character.
Or use proc contents ans see the SAS Log for detailed columns.

 

Att

Contributor
Posts: 47

Re: Round numbers sum

Posted in reply to DartibaliRodrigo

Thank you

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 871 views
  • 1 like
  • 4 in conversation