Hi, I need help with creating frequency table (count and %) by treatment groups
ID | group | age | weight | gender | race | ethnicity | diabetes |
1 | placebo | 25 | 150 | female | white | hispanic | yes |
2 | treatment | 35 | 123 | male | asian | not hispanic | no |
3 | placebo | 23 | 153 | female | unknown | hispanic | no |
4 | placebo | 34 | 124 | male | white | hispanic | yes |
5 | treatment | 27 | 110 | male | white | not hispanic | yes |
6 | treatment | 44 | 135 | male | asian | not hispanic | yes |
7 | treatment | 23 | 100 | female | asian | not hispanic | no |
The denominator for placebo is 3 and treatment is 4. Something like?
placebo_n | placebo_pct | treatment_n | treatment_pct | |
female | 2 | 66.7% | 1 | 25.0% |
male | 1 | 33.3% | 3 | 75.0% |
asian | 3 | 75.0% | ||
unknown | 1 | 33.3% | ||
white | 2 | 33.3% | 1 | 25.0% |
I tried proc freq data=test; by treatment; tables gender*treatment race*treatment;run;
Complex tables like this aren't going to come directly from PROC FREQ and PROC MEANS. You probably ought to consider using PROC REPORT or PROC TABULATE, but even so, I don't think either of those can produce your tables exactly as you show them. You may need to either re-design the tables, or use the results of PROC FREQ and PROC MEANS in a Data step, where you can program the exact appearance of the tables as you want them.
I guess this requires more discussion, how exactly do the SAS outputs have to match what you show, and how much work are you willing to put into it to get there.
Proc tabulate can do this:
proc format ;
picture pct low-high='000.0%';
run;
proc tabulate data=have noseps;
class group gender race;
tables gender='00'x
race='00'x
, group=' '*(N='N'*f=comma6.0 colpctn='Pct'*f=pct7.0)
/rts=10 ;
run;
Yes, SAS has its own standard PERCENT format, but it converts .333 to 33.3%, and converts 333 to 3333%. And when proc tabulate generates percents, they range from 0 to 100, not 0 to 1. So I had to make my own format (unless someone can show me an equivalent sas-supplied format).
Hi @monday89
If you want to create a table you can try the following code:
data test;
input ID 1 group $ 3-11 age 13-14 weight 16-18 gender $ 20-25 race $ 27-33 ethnicity $ 35-46 diabetes $ 48-51;
datalines;
1 placebo 25 150 female white hispanic yes
2 treatment 35 123 male asian not hispanic no
3 placebo 23 153 female unknown hispanic no
4 placebo 34 124 male white hispanic yes
5 treatment 27 110 male white not hispanic yes
6 treatment 44 135 male asian not hispanic yes
7 treatment 23 100 female asian not hispanic no
;
run;
/* compute frequencies */
proc freq data=test;
tables gender * group / out= group_gender (drop=percent);
tables race * group / out= group_race (drop=percent);
run;
/* compute percentages */
proc sql;
create table group_gender_n_pct as
select a.gender,a.group,a.count,a.count/b.total as percent format=percent8.2
from group_gender as a full join
(select group, sum(count) as total from group_gender group by group) as b
on a.group = b.group
order by gender;
create table group_race_n_pct as
select a.race,a.group,a.count,a.count/b.total as percent format=percent8.2
from group_race as a full join
(select group, sum(count) as total from group_race group by group) as b
on a.group = b.group
order by race;
quit;
/* transpose data */
data group_gender_tr;
length category $20.;
merge group_gender_n_pct (where=(group="treatment") rename=(count = treatment_n percent=treatment_pct))
group_gender_n_pct (where=(group="placebo") rename=(count = placebo_n percent=placebo_pct));
by gender;
category = gender;
drop group gender;
run;
data group_race_tr;
length category $20.;
merge group_race_n_pct (where=(group="treatment") rename=(count = treatment_n percent=treatment_pct))
group_race_n_pct (where=(group="placebo") rename=(count = placebo_n percent=placebo_pct));
by race;
category = race;
drop group race;
run;
/* merge tables related to gender and race : table want */
data want;
set group_gender_tr group_race_tr;
run;
proc print data=want;
id category;
var placebo_n placebo_pct treatment_n treatment_pct;
run;
You can also create a table from PROC TABULATE as described by @mkeintz :
proc format ;
picture pct low-high='000.0%';
run;
proc tabulate data=test out=have_count noseps;
class group gender race;
tables gender='00'x
race='00'x
, group=' '*(N='N'*f=comma6.0 colpctn='Pct'*f=pct7.0)
/rts=10 ;
run;
data have_count_tr (drop= gender race _type_ _page_ _table_ PctN_100);
format percent percent8.2;
set have_count;
length category $20.;
if gender ne "" then category = gender;
else category = race;
percent = PctN_100 / 100;
run;
proc sort data=have_count_tr;
by category;
run;
data want;
merge have_count_tr (where=(group="treatment") rename=(n = treatment_n percent = treatment_pct))
have_count_tr (where=(group="placebo") rename=(n = placebo_n percent = placebo_pct));
by category;
drop group;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.