Modifying proc template picture statement for crosstabulation rowpercent values

[abaker_ca]

I am trying to modify a picture statement in my proc template code so that values in my proc tabulate output where there are no observations for a combination in a crosstabulation appear as 0 for the count and  (0.0%) for the rowpercent contents.

Here is a toy data set and my proc template and proc tabulate codes. Can someone help me figure out how to get the cell for the 'Site A' and 'POS' combination to be represented as (0.0%) for the percent row and just a zero for the count row?

data want;

input id 1-1 site $4 result $6-8;

datalines;

1  A NEG

2  A NEG

3  B POS

4  C NEG

5  C POS

6  A NEG

7  B POS

8  C POS

9  D POS

10 B NEG

11 C NEG

12 A NEG

13 B POS

14 C NEG

15 B POS

16 B NEG

17 D POS

18 D NEG

19 B POS

20 C NEG

;

run;

proc format;

picture pctfm (round) low-high='0000.0%)'

(prefix='(');

run;

proc tabulate data=want;

class site result;

table ALL = 'N Per Site' result * (N COLPCTN='%'*F=pctfm.),

  site;

run;

[ballardw · Accepted solution]

I am not a 100 percent sure I understand exactly what appearance you want. Is this close?

 
proc format;
picture pctfm (round) 
low-high='0000.0%)' (prefix='(')
. = '(0.0%)' (noedit)
;
run;
 
proc tabulate data=want;
class site result;
table ALL = 'N Per Site' result * (N COLPCTN='%'*F=pctfm.),
  site / misstext='0';
run;

The NOEDIT option has the digits in the format display exactly as written and not treated as digit selectors and use of the single . indicates only applies to that value.

The MISSTEXT tabulate table option would apply to all other "missing" values displayed. So depending on the complexity of the table you might want a custom format for the N statistic instead of the table option. The format and code might look like:

 
proc format;
picture pctfm (round) 
low-high='0000.0%)' (prefix='(')
. = '(0.0%)' (noedit)
;

value zeromiss
. = '0'
;

run;
 
proc tabulate data=want;
class site result;
table ALL = 'N Per Site' result * (N*f=zeromiss. COLPCTN='%'*F=pctfm.),
  site / ;
run;

Ranges of values might want a Default length or explicit width when using the custom format Zeromiss.

BTW may want to edit the title of the thread to remove TEMPLATE and say FORMAT.

[abaker_ca]

Wow. This worked exactly how I needed it to. I am very appreciative for your solution and will see if I can change the title of my post to reflect using format instead of template.

[ballardw]

I think if you right click on the three-line icon at your subject heading you get an "edit" option. Select that and retype in the subject line.