Round numbers sum

[lerdem]

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

[DartRodrigo]

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

[lerdem]

When i do format=best20. it gives me

 -7.10542865412511E-14

Another solution?

[DartRodrigo]

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

[Steelers_In_DC]

Round() is a sas function:

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

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

[RW9]

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;

[DartRodrigo]

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

[lerdem]

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

[DartRodrigo]

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

[RW9]

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.

[lerdem]

It gave me this error in the atachment,

[RW9 · Accepted solution]

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.

[DartRodrigo]

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

[lerdem]

Thank you