Programming the statistical procedures from SAS

Struggling with SAS procedure and data

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Struggling with SAS procedure and data

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.


Accepted Solutions
Solution
‎09-20-2011 09:14 PM
PROC Star
Posts: 7,417

Re: Struggling with SAS procedure and data

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


All Replies
Super User
Posts: 18,583

Struggling with SAS procedure and data

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.

Contributor
Posts: 33

Re: Struggling with SAS procedure and data

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

PROC Star
Posts: 7,417

Re: Struggling with SAS procedure and data

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;

Contributor
Posts: 33

Re: Struggling with SAS procedure and data

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.

Super User
Posts: 18,583

Re: Struggling with SAS procedure and data

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;

PROC Star
Posts: 7,417

Re: Struggling with SAS procedure and data

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

Contributor
Posts: 33

Re: Struggling with SAS procedure and data

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.

PROC Star
Posts: 7,417

Re: Struggling with SAS procedure and data

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;

Solution
‎09-20-2011 09:14 PM
PROC Star
Posts: 7,417

Re: Struggling with SAS procedure and data

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%

Contributor
Posts: 33

Re: Struggling with SAS procedure and data

Thanks a ton!! You guyz are awesome Smiley Happy

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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