DATA Step, Macro, Functions and more

Proc Tabulate multiple variables- Missing not counted to percents

Reply
New Contributor
Posts: 4

Proc Tabulate multiple variables- Missing not counted to percents

 

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 Smiley Happy

PROC Star
Posts: 1,760

Re: Proc Tabulate multiple variables- Missing not counted to percents

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;
Super User
Posts: 10,044

Re: Proc Tabulate multiple variables- Missing not counted to percents

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;
Ask a Question
Discussion stats
  • 2 replies
  • 194 views
  • 0 likes
  • 3 in conversation