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

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. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

7 REPLIES 7
SASKiwi
PROC Star

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
Opal | Level 21

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
TARASK
Fluorite | Level 6

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

Kurt_Bremser
Super User

@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
Fluorite | Level 6
Thanks For Your Response. I am not deviding by zero. The numerator is zero.
TARASK
Fluorite | Level 6
Pgstats

The sum_weight is zero. But I will change the format . Thank you.
TARASK
Fluorite | Level 6
I have not posted the entire cide, it gets complicated. The variable is created. I like upper case😂

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 7 replies
  • 2996 views
  • 1 like
  • 4 in conversation