BookmarkSubscribeRSS Feed
UKPete
Calcite | Level 5
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;
4 REPLIES 4
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Super User
Ask for the statistic NMISS for the variables with missing values.
Ksharp
Super User
For your situation.I think proc report is a better choice.And Cynthia@sas will give your help.
proc report + completecolumns



Ksharp
Ksharp
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1562 views
  • 0 likes
  • 4 in conversation