Excluding missing from the percentages using Proc Tabulate

[UKPete]

I need to produce tables using Proc Tabulate with %s which exclude the missing values. Using the MISSING option will shown the number of missing values, but includes these in the denominator for calculating percentages. Removing the MISSING option gives the %s I want, but does not show the number of missing values.

In the example below I would the output to have 25% and 80% for the 'Diabetic' row and no %s in the 'Unknown' row.

In the past I have done 2 proc tabulates, one after the other and then edited the output. However in the brave new world of ODS RTF I need output which does not need editing.

Any suggestions would be appreciated, even if they are not very neat.

data test;
input patient sex $ Diabetes ;
cards;
1 M 1
2 M .
3 M 1
4 M 1
5 M 2
6 M 1
7 F .
8 F 2
9 F 2
10 F 2
11 F 1
12 F .
;
run;

proc format;
value diabetes
1='Diabetic'
2='Non-diabetic'
.='Unknown'
;

proc tabulate FORMCHAR=' ' missing;
class sex diabetes;
format diabetes diabetes.;
table diabetes all,sex*(N*F=5.0 colpctn) ;
run;

[SPR]

Hello UKPete,

It looks like proc FREQ does the trick. It creates the FREQ dataset that contains percentages and counts you need. I do not konw proc TABULATE so have to stop on this point letting you to create a final report from the FREQ dataset:
[pre]
proc sort data=test;
by sex;
run;
proc freq data=test noprint;
tables diabetes/out=freq;
format diabetes diabetes.;
by sex;
run;
[/pre]
Sincerely,
SPR

[ballardw]

Ask for the statistic NMISS for the variables with missing values.

[Ksharp]

For your situation.I think proc report is a better choice.And Cynthia@sas will give your help.
proc report + completecolumns



Ksharp

[Ksharp]

OK.
I will try it. It is much complicated.

[pre]
data test;
input patient sex $ Diabetes ;
cards;
1 M 1
2 M .
3 M 1
4 M 1
5 M 2
6 M 1
7 F .
8 F 2
9 F 2
10 F 2
11 F 1
12 F .
;
run;

proc format;
value diabetes
.='Unknown'
1='Diabetic'
2='Non-diabetic'

;
run;
ods pdf file='c:\x.pdf' style=sasweb;
proc report data=test nowd completerows out=see;
column diabetes sex,(n percent);
define diabetes /group format=diabetes. preloadfmt exclusive missing order=internal;
define sex /across;
define percent/'ColPctN' computed format=percent8.;
compute before;
sum1=_c2_ ;
sum2=_c4_;
endcomp;
compute percent;
if missing(diabetes) then do;
_sum1=sum1-_c2_;
_sum2=sum2-_c4_;
end;
else do;
_c3_=_c2_/_sum1;
_c5_=_c4_/_sum2;
end;
endcomp;
compute after /style={just=right asis=on};
str=cat('All : ',sum1,' 100% ',sum2,' 100%');
line str $200.;
endcomp;
run;
ods pdf close;
[/pre]



Ksharp