Fluorite | Level 6

## ZERO DIVISION BY A NUMBER,USING PROC SQL

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

## Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

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.

PG
7 REPLIES 7
PROC Star

## Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

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,``````

Opal | Level 21

## Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

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.

PG
Fluorite | Level 6

## Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

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

Super User

## Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

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;

sum_pieces is never created in your code.

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

Lowercase code is easier to read.

Fluorite | Level 6

## Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

Thanks For Your Response. I am not deviding by zero. The numerator is zero.
Fluorite | Level 6

## Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

Pgstats

The sum_weight is zero. But I will change the format . Thank you.
Fluorite | Level 6

## Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

I have not posted the entire cide, it gets complicated. The variable is created. I like upper case😂
Discussion stats
• 7 replies
• 2996 views
• 1 like
• 4 in conversation