PROC FORMAT missing value

[lin39]

Proc Tabulate used to display zero's but after applying a format, zero's are now replaced by a blank cell.  How do I get it display '0' again?  Thanks!

** test zero;

proc format;
picture test_fmt
 low-high = '000,000,000'
 other =  '0' /* Is this the correct way to indicate 0 in place for missing? */
;
run;

data test;
set sashelp.shoes;
if region = 'Asia' then returns = 0;
run;

proc tabulate data=test;  
  where region in ('Asia', 'Canada') and product in ('Boot');
  class product region;
  var sales returns ;
  table (region all),
        ALL * n
	All='No.(%)'*(sales returns) * (sum * f=test_fmt. mean) 
        / ROW=FLOAT RTS=22 MISSTEXT='0' 
	;
run;

[ballardw · Accepted solution]

proc format;
picture test_fmt
low-high = '000,000,009'
 . =  '0'
;
run;

Maybe what you want.

But perhaps a full description of what you are attempting would help.

If the only purpose is to prevent the sum showing as 0.00 for the misstext=0  value then use one of the BEST. , or if you want commas, Comma11. format for the sum statistic. Both will suppress the trailing zeroes.

[Tom]

So you have two problems with your picture format.

In the main range you are causing zero to print as blanks.

In the other range you didn't tell it that you want it to treat zero in the picture as the literal zero digit instead of a place holder for digits in the answer.

Try this little data step with your current format.

data _null_;
  do x=-2,.,0,2 ;
    put '|' x test_fmt11. '| ' x= ;
  end;
run;

Here is an updated picture format.

proc format;
picture test_fmt
 low-high = '000,000,009'
 other =  '0' (noedit)
;
run;

Or why not just use a normal format instead.

proc format;
value test_fmt
 low-high = [comma11.]
 other =  '          0' 
;
run;

[lin39]

Thanks for suggesting the cool data step to try with my format.  You guys solved my problem.