BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nupur20
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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:

RoleResponse_Rate
A(1) 50%
B(2) 50%
C(0) 0%

View solution in original post

10 REPLIES 10
Reeza
Super User

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.

Nupur20
Calcite | Level 5

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

art297
Opal | Level 21

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;

Nupur20
Calcite | Level 5

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.

Reeza
Super User

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;

art297
Opal | Level 21

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:

RoleResponse_Rate
A(1)0.5
B(2)0.5
C(0)0

Nupur20
Calcite | Level 5

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.

art297
Opal | Level 21

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;

art297
Opal | Level 21

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:

RoleResponse_Rate
A(1) 50%
B(2) 50%
C(0) 0%

Nupur20
Calcite | Level 5

Thanks a ton!! You guyz are awesome Smiley Happy

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!

What is ANOVA?

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.

Discussion stats
  • 10 replies
  • 1662 views
  • 0 likes
  • 3 in conversation