Hi ,
Im piggy-backing on this post:
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 🙂
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.