ZERO DIVISION BY A NUMBER,USING PROC SQL

[TARASK]

hi,

I am using proc sql to divide two variables where the numerator is zero and the answer should be zero but I get a non zero number.

The value of 'Sum_weight' is zero for a particular country and when I divide it by sum_pieces, the result should be zero: 0/number=0

PROC SQL;
CREATE TABLE TEST AS
SELECT ORIG_COUNTRY,QUARTER,
SUM(GROSS_WEIGHT_KG) AS SUM_WEIGHT LABEL="SUM OF KG_WEIGHT" FORMAT=8.0,
CALCULATED SUM_WEIGHT/ CALCULATED SUM_PIECES AS AWPP FORMAT=8.2
FROM OAPQ1Q2
GROUP BY ORIG_COUNTRY ,QUARTER;
QUIT;

Thank you in advance. 

[SASKiwi]

Mathematically you can't divide by 0 - SAS is giving you the mathematical answer of a missing value as the result of an invalid operation.

If you want to have the result as 0 by ignoring the maths then try a CASE statement:

case
  when CALCULATED SUM_PIECES = 0 then 0
  else CALCULATED SUM_WEIGHT / CALCULATED SUM_PIECES
end
as AWPP format 8.2,

[PGStats · Accepted solution]

If sum_weight is smaler than 0.5 it will show as zero with format 8.0.  Change the format to BEST. to see what the real value is.

[TARASK]

I removed the format and it works. The real value of sum_weight was not zero. Thank you. 

[Kurt_Bremser]

---

@TARASK wrote:

hi,

I am using proc sql to divide two variables where the numerator is zero and the answer should be zero but I get a non zero number.

The value of 'Sum_weight' is zero for a particular country and when I divide it by sum_pieces, the result should be zero: 0/number=0

PROC SQL;
CREATE TABLE TEST AS
SELECT ORIG_COUNTRY,QUARTER,
SUM(GROSS_WEIGHT_KG) AS SUM_WEIGHT LABEL="SUM OF KG_WEIGHT" FORMAT=8.0,
CALCULATED SUM_WEIGHT/ CALCULATED SUM_PIECES AS AWPP FORMAT=8.2
FROM OAPQ1Q2
GROUP BY ORIG_COUNTRY ,QUARTER;
QUIT;

Thank you in advance. 

---

sum_pieces is never created in your code.

And stop shouting at the computer (coding in all uppercase) 😉

Lowercase code is easier to read.

[TARASK]

Thanks For Your Response. I am not deviding by zero. The numerator is zero.

[TARASK]

Pgstats

The sum_weight is zero. But I will change the format . Thank you.

[TARASK]

I have not posted the entire cide, it gets complicated. The variable is created. I like upper case😂