topic Re: PROC FORMAT missing value in ODS and Base Reporting

PROC FORMAT missing value

lin39 — Wed, 27 Sep 2017 18:42:25 GMT

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;

Re: PROC FORMAT missing value

ballardw — Wed, 27 Sep 2017 19:15:29 GMT

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.

Re: PROC FORMAT missing value

Tom — Wed, 27 Sep 2017 19:22:59 GMT

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;

Re: PROC FORMAT missing value

lin39 — Wed, 27 Sep 2017 20:48:31 GMT

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