BookmarkSubscribeRSS Feed
nrozario
Calcite | Level 5

 

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 🙂

2 REPLIES 2
ChrisNZ
Tourmaline | Level 20

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

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 778 views
  • 0 likes
  • 3 in conversation