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
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.
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
When i do format=best20. it gives me
-7.10542865412511E-14
Another solution?
Then use the round() function wthin the sum()
Round() is a sas function:
data have;
one = 1.0000000001;
two = 1.0000000002;
run;
data want;
set have;
want = round(sum(one,two));
run;
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;
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
I don't have to do round. I just want to see a nice number. What format should i use
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
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.
It gave me this error in the atachment,
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.
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
Thank you
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.