Proc Tabulate multiple variables- Missing not counted to percents

[nrozario]

Hi ,

Im piggy-backing on this post:

https://communities.sas.com/t5/SAS-Procedures/PROC-TABULATE-Missing-as-Percent-of-Non-Missing/m-p/27...

I have the same problem but if I multiple variables in the same proc tabulate and they have missing observations for different variables. Ideally I would love to have proc tabulate to tweak the "missing" option. I added a variable pay to the dataset and would like to have the output below.

Any suggestions how to do it in proc tabulate?

Is there a way instead of using proc sql based on the post above?

Or i would need to learn proc report?

Dataset and desired output below

proc format;
value $ge
'F' = "Female"
'M' = "Male"
other = "Unknown";
value pctfmt
low -< 1.0 = [percentn7.1]
1.0 - high = "100.0%";
run;

data demos;
input @1 grp $1.
@3 gender $1.
@5 pay $1.
;
datalines;
A F M
A F D
A F M
A F D
A M
A M D
A     D
B F M
B F D
B F D
B M
B     D
B     M
;
run;

Desired Output(in the same proc tabulate - not necessary to get the Unknown(missing) row):

Gender	A		B
Female	4	66.70%	3	75.00%
Male	2	33.30%	1	25.00%
Unknown	1	14.29%	2	33.33%
TOTAL	7	100.00%	6	100.00%
Pay
D	4	66.67%	3	60.00%
M	2	33.33%	2	40.00%
Unknown	1	14.29%	1	16.67%
TOTAL	7	100.00%	6	100.00%

Thanks 🙂

[ChrisNZ]

For this level of unorthodox processing, you have to do it manually.  Something like this:

proc summary data=DEMOS missing chartype;
  class GRP GENDER PAY;
  types GRP * (GENDER PAY) 
        GRP ;
  output out=SUM;
  format GENDER $ge. PAY $pay.  ;
run;

data PCT;
  length PAY GENDER $8;
  retain POP_GRP POP_MISS;
  set SUM(in=A where=(GRP='A')) 
      SUM(in=B where=(GRP='B')) ;
  by GRP _TYPE_;
  if _TYPE_='100'                 then POP_GRP  = _FREQ_;
  if _TYPE_='101' & PAY    eq ' ' then POP_MISS = _FREQ_;
  if _TYPE_='101' & PAY    eq ' ' then POP_PCT  = POP_MISS/POP_GRP;
  if _TYPE_='101' & PAY    ne ' ' then POP_PCT  = _FREQ_/(POP_GRP-POP_MISS);
  if _TYPE_='101' & PAY    eq ' ' then PAY='Unknown';

  if _TYPE_='110' & GENDER eq ' ' then POP_MISS = _FREQ_;
  if _TYPE_='110' & GENDER eq ' ' then POP_PCT  = POP_MISS/POP_GRP;
  if _TYPE_='110' & GENDER ne ' ' then POP_PCT  = _FREQ_/(POP_GRP-POP_MISS);
  if _TYPE_='110' & GENDER eq ' ' then GENDER='Unknown';
  if A then do; POP_A=_FREQ_; PCT_A=POP_PCT; end;
  if B then do; POP_B=_FREQ_; PCT_B=POP_PCT; end;
  drop POP_MISS POP_MISS POP_PCT _FREQ_;
  format _ALL_;
run;

[Ksharp]

I am afraid adding one more SQL is best the choice. It looks like PROC TABULATE can not use PCTN<> get the right output.

data demos;
input @1 grp $1.
@3 gender $1.
@5 pay $1.
;
datalines;
A F M
A F D
A F M
A F D
A M
A M D
A     D
B F M
B F D
B F D
B M
B     D
B     M
;
run;
data temp;
length gender $ 40;
set demos(keep=grp gender) demos(keep=grp pay rename=(pay=gender) in=inb);  
if inb then in='Pay   ';
 else in='Gender';
if not inb then do;
 if gender='F' then gender="Female";
  else  if gender='M' then gender="Male";
    else gender='Unknown';
end;
run;
proc sql;
 create table want as
  select *,1/count(*) as p
   from temp
    group by in,grp;
quit;
proc tabulate data=want format=comma10. missing order=data;
   class in gender grp /preloadfmt ;
   var p;
   table in=''*(gender=' ' all), grp=''*(n='' p=''*sum=''*format=percent8.2) / box='' misstext=' ';
   keylabel all='TOTAL'  ;
run;