I have another issue with MISSING ON. My table output has an ON TABLE SUMMARIZE. When I apply the MISSING ON to some of the COMPUTE fields, the summarize Total for those compute fields displays ***** instead of "-" character. Anyway to work in MISSING ON with summarize totals?

Thanks,

Joe

This message has been edited. Last edited by: JOE,

So basically if the entire column just happens to have the MISSING ON character, the total of that column is ******. How do I replace the total with the MISSING ON character?

Joe,

First:
****** usually means the result of ( in this case ) the summarized compute doesn't fit the format.

e.g. if the sum is 100000 and the format is D2 it doesn't fit.

Second:
Remember "summarize" works on the totals of the columns used in the compute. If some valid value is in those totals the COMPUTE will use that.

MISSING ON only allows the result of the COMPUTE to be 'missing'. But if there's anyway WF can figure out to show a value, it will.

MISSING ON NEEDS ALL DATA is more strict. ALL fields used must be available. If not 'MISSING' is used.


Greets, G'luck,
Dave

Dave,
I'm sorry, the MISSING ON is actually the result of a division calculation from two columns on the report..not a total of one column. I misinformed you. The MISSING ON is to prevent the divide by zero error message. If there is a better way to do this than missing on, please advise...thanks

Joe

You could test the value of the divisor before performing the division:

COMPUTE XYZ_VALUE/D12.2 = IF (XYZ_DENOMINATOR EQ 0) THEN 0 ELSE (XYZ_NUMERATOR / XYZ_DENOMINATOR);

Setting %STRICTMATH can be used to return zero when dividing by zero.

SET %STRICTMATH = OLD

@JOE

It's okay. I guess all of us have a job that requires to keep asking to find out the real question.

Dan gave you two solutions.
( I prefer and use the first, but I don't never show a '0', rather a 'missing' to prevent misinterpretation. )

Greets,Dave

Thanks for all your responses. We really dont want to display 0's which may reflect on poor performance. That's why we were looking to replace the error with a - instead of zero.

You can also suppress (S) display of the zeroes via the format definition for the COMPUTEd/DEFINEd field:

COMPUTE XYZ_VALUE/D12.2S = IF (XYZ_DENOMINATOR EQ 0) THEN 0 ELSE (XYZ_NUMERATOR / XYZ_DENOMINATOR);

Sometimes a 0 is the actuall value.
I prefer:

COMPUTE XYZ/D12.2 MISSING ON = IF ( DENOMINATOR EQ 0 ) THEN MISSING ELSE NUMERATOR / DENOMINATOR;

Greets,
Dave

Thanks for all the replies. We decided to conditionally format out the *** (which are negative values) with a conditional format to make it the same color as the total line.