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;
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.
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.