topic Re: ZERO DIVISION BY A NUMBER,USING PROC SQL in SAS Programming

ZERO DIVISION BY A NUMBER,USING PROC SQL

TARASK — Tue, 18 Sep 2018 20:49:56 GMT

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.

Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

SASKiwi — Tue, 18 Sep 2018 21:05:47 GMT

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,

Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

PGStats — Tue, 18 Sep 2018 21:06:19 GMT

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.

Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

Kurt_Bremser — Wed, 19 Sep 2018 05:53:12 GMT

@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.

Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

TARASK — Wed, 19 Sep 2018 11:48:40 GMT

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

Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

TARASK — Wed, 19 Sep 2018 11:51:38 GMT

Pgstats

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

Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

TARASK — Wed, 19 Sep 2018 11:54:32 GMT

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

Re: ZERO DIVISION BY A NUMBER,USING PROC SQL

TARASK — Wed, 19 Sep 2018 20:45:03 GMT

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