The SAS Output Delivery System and reporting techniques

PROC FORMAT missing value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

PROC FORMAT missing value

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;

Accepted Solutions
Solution
4 weeks ago
Super User
Posts: 11,134

Re: PROC FORMAT missing value

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.

View solution in original post


All Replies
Solution
4 weeks ago
Super User
Posts: 11,134

Re: PROC FORMAT missing value

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.

Super User
Super User
Posts: 6,848

Re: PROC FORMAT missing value

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;
Highlighted
Occasional Contributor
Posts: 18

Re: PROC FORMAT missing value

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 286 views
  • 1 like
  • 3 in conversation