Hi I have a data in SAS like this:
Role Answer
A -
A Diabetes
B -
B Diabetes
B Diabetes
B -
C -
C -
As you can see Out of role A only one answered the question, so the response rate would be 50%. Similarly in role B two people answered question, the response rate would be 33.33% and for C 0%.
My question is how can I combine similar rwos so that my data would look like this:
Role Response Rate
A 1(50%)
B 2 (33%)
C 0 (0%)
OR
A B C
Reponse Rate 1(50%) 2(33%) 0(0%)
I have tried eerything I know about SAS. I wouold really appreciate your help in this.
or, to precisely match your original example:
proc sql;
select role, catt("(",n(answer),") ",
put(100*n(answer)/count(role),6.),
"%")
as Response_Rate
from have
group by role
;
quit;
which produces:
Role | Response_Rate |
---|---|
A | (1) 50% |
B | (2) 50% |
C | (0) 0% |
There's many ways.
Here's one two step procedure.
1. Run a proc freq with the missing option
proc freq data=have noprint;
tables role*answer/missing out=sample outpct;
run;
2. Process data to format you need
data want;
set sample;
where answer ne .;
response_rate=put(count, 8.)||"("||put(pct_row/100, percent8.)||")";
run;
That should help you get started.
Thanks! this code helped me with columns that were not blank but for the blank responses, for eg: in role c, the code counted the frequency as 2 (because of 2 blanks) and percent as 100% instead of giving 0(0%).
Could you help me fix this? Thanks again
I probably don't understand what you want as, when I look at your data for role 'b', two out of 4 people answered thus I'd expect a frequency of 50%.
If that is really the number you want try something like:
proc sql noprint;
create table want as
select role, n(answer) as frequency,
n(answer)/count(role) as Response_Rate
from have
group by role
;
quit;
Yes, What you thought was absolutely right. The code gave me 50% for role B bur for role C, since nobody answered the questions (answer values are blank in the data), its giving me a 100% frequency instead of 0%. Thats what I meant in my last post.
I reallt apprecitae your time and would be greatful if you could help me in this.
Try adding the sparse option, but then you'll probably need to modify your where clause as well.
proc freq data=have noprint;
tables role*answer/missing out=sample outpct sparse;
run;
If you just want the printout use:
proc sql;
select role, catt("(",n(answer),") ",
n(answer)/count(role)) as Response_Rate
from have
group by role
;
quit;
It results in:
Role | Response_Rate |
---|---|
A | (1)0.5 |
B | (2)0.5 |
C | (0)0 |
Thanks a ton!! But can I get 0.5, 0.5 and 0 in percentages.
Its a big report that I have to submit and my office has standard format for this.
Sorry for being a pain.
You can add whatever format is needed. E.g.,
proc sql;
select role, catt("(",n(answer),") ",
put(n(answer)/count(role),percent6.1))
as Response_Rate
from have
group by role
;
quit;
or, to precisely match your original example:
proc sql;
select role, catt("(",n(answer),") ",
put(100*n(answer)/count(role),6.),
"%")
as Response_Rate
from have
group by role
;
quit;
which produces:
Role | Response_Rate |
---|---|
A | (1) 50% |
B | (2) 50% |
C | (0) 0% |
Thanks a ton!! You guyz are awesome
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!
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.