topic Re: How can I not display percentages of missings categories using proc tabulate? in SAS Procedures

How can I not display percentages of missings categories using proc tabulate?

Clara — Mon, 24 Oct 2011 09:11:07 GMT

I desperately need your help:

I am using proc tabulate to produce simple frequency tables.
Here, the overall non-missing have to be the 100%.

Example:

Yes                                      7   70.0%
No                                       3   30.0%
Missing                                2   20.0%
Overall (non-missing)       10 100.0%
TOTAL                               12 -------

I use a picture format to format all percentages more than 100% with "-------".

But I have not succeeded to replace with "------" the percentages of missings.

Does anyone have a trick for this?

Thank you!
Clara

How can I not display percentages of missings categories using proc tabulate?

Ksharp — Mon, 24 Oct 2011 10:17:27 GMT

I do not understand your last sentence.

Whether the following output is what you want?

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;

Ksharp

Re: How can I not display percentages of missings categories using proc tabulate?

Clara — Mon, 24 Oct 2011 15:18:55 GMT

I want and must do it using proc tabulate. See my example:

data test;
input patnr yesno ;
cards;
1 1
2 .
3 1
4 1
5 2
6 2
7 .
8 2
9 2
10 2
11 1
12 .
;
run;
data test;
set test;
   if yesno>.z then id=1;
run;

proc format;
value yesno_ (multilabel)
.='Missing'
1,2=' Overall non-missing'
1=' Yes'
2=' No';
picture pct (round)        .-.z ='9.9' (noedit)
                              0 ='9.9' (mult=100 )
                      0<-0.009999='<0.01' (noedit)
                     0.01-0.99999='9.9' (mult=100 )
                          1-100='000.0'
                       101-high='----';
run;

proc tabulate data=test format=5. missing order=formatted;
var id ;
class yesno / mlf;
keyword n pctn all;
table   (yesno ='' all='TOTAL') ,
        (n='N' pctn<yesno*id all*id id>='%'*f=pct.)
          /rts=40
          box=[label=" "]
               printmiss misstext='0' row=float indent=3 contents='';
format   yesno yesno_. ;

run;

How can I not display percentages of missings categories using proc tabulate?

robby_beum — Mon, 24 Oct 2011 15:31:55 GMT

I too am having a hard time understanding your last sentence - I'm pretty dense ;o)

What happens when you use the bold text below in your code?

proc tabulate data=miss;

class ptn visit;

var score1;

table ptn='Patient Id',

mean=' '*score1='Drug A'* visit='Visit'/ misstext=[label="Missing"] RTS=25.;

format visit vi.;

run;

Re: How can I not display percentages of missings categories using proc tabulate?

Clara — Mon, 24 Oct 2011 17:46:49 GMT

I use proc tabulate (code above) to create a following table

N %

Yes 4 44.4

No 5 55.6

Overall non-missing 9 100.0

Missing 3 33.3

TOTAL 12 ----

And what I want: replace the percents of missing with "----".

I hope, you could understand my problem now.

Nothing happens when I use misstext=[label="Missing"] .

Re: How can I not display percentages of missings categories using proc tabulate?

art297 — Mon, 24 Oct 2011 18:26:17 GMT

Do you only need to do this for this one specific report this one time? Or are you trying to develop code that will take care of all situations?

In the event that this is just a one time specific to this report problem, and if the percent missing is unique, you could always just capture it in your format statement. e.g.:

data test;

input patnr yesno ;

cards;

1 1

2 .

3 1

4 1

5 2

6 2

7 .

8 2

9 2

10 2

11 1

12 .

;

run;

data test;

set test;

if yesno>.z then id=1;

run;

proc format;

value yesno_ (multilabel)

.='Missing'

1,2=' Overall non-missing'

1=' Yes'

2=' No';

picture pct (round) .-.z ='9.9' (noedit)

0 ='9.9' (mult=100 )

0<-0.009999='<0.01' (noedit)

0.01-0.99999='9.9' (mult=100 )

1-33.2999999='000.0'

33.29999991-33.333333339='----'

33.333333391-100='000.0'

101-high='----';

run;

proc tabulate data=test format=5. missing order=formatted;

var id ;

class yesno / mlf;

keyword n pctn all;

table (yesno ='' all='TOTAL') ,

(n='N' pctn<yesno*id all*id id>='%'*f=pct.)

/rts=40

box=[label=" "]

printmiss misstext='0' row=float indent=3 contents='';

format yesno yesno_. ;

run;

How can I not display percentages of missings categories using proc tabulate?

Clara — Mon, 24 Oct 2011 18:44:26 GMT

I want that for all situations. Above was just an example to explain my problem.

How can I not display percentages of missings categories using proc tabulate?

ballardw — Mon, 24 Oct 2011 21:44:09 GMT

I think you are at a point where you need to show exactly what your desired output is not what you have currently done.

How can I not display percentages of missings categories using proc tabulate?

Ksharp — Tue, 25 Oct 2011 03:35:20 GMT

You can get it by using proc tabulate twice, if you don't mind.

data test;
input patnr yesno ;
cards;
1 1
2 .
3 1
4 1
5 2
6 2
7 .
8 2
9 2
10 2
11 1
12 .
;
run;
data test;
  set test;
   if yesno>.z then id=1;
run;


proc format;
value yesno_  (multilabel)

  .='Missing'
1,2=' Overall non-missing'
  1='  Yes'
  2=' No';

picture pct (round)        .-.z ='9.9' (noedit)
                              0 ='9.9' (mult=100 )
                      0<-0.009999='<0.01' (noedit)
                     0.01-0.99999='9.9' (mult=100 )
                          1-100='000.0'
                       101-high='----';
run;


proc tabulate data=test format=5.  missing order=formatted out=temp;
  var id ;
  class  yesno  / mlf;
  keyword n pctn all;

table   (yesno ='' all='TOTAL') ,
        (n='N' pctn='%'*f=pct.)
          /rts=40
          box=[label=" "]
               printmiss misstext='0' row=float indent=3 contents='';

format   yesno yesno_. ;
run;
data temp;
 set temp(rename=(n=_n));
 if yesno='Missing' then pctn_0=140;
 if missing(yesno) then yesno='TOTAL';
run;
proc tabulate data=temp;
 class yesno;
 var _n pctn_0;
 table yesno=' ',_n='N'*f=best. pctn_0='%'*format=pct.;
 keylabel sum=' ';
run;

Ksharp