BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lerdem
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

13 REPLIES 13
DartRodrigo
Lapis Lazuli | Level 10
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
Quartz | Level 8

When i do format=best20. it gives me

 -7.10542865412511E-14

Another solution?

DartRodrigo
Lapis Lazuli | Level 10

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

Steelers_In_DC
Barite | Level 11

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

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
Quartz | Level 8

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

DartRodrigo
Lapis Lazuli | Level 10

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

It gave me this error in the atachment,


error.PNG
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

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
Quartz | Level 8

Thank you

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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