Solved
Contributor
Posts: 33

# Struggling with SAS procedure and data

Hi I have a data in SAS like this:

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,628

## Re: Struggling with SAS procedure and data

or, to precisely match your original example:

proc sql;

"%")

as Response_Rate

from have

group by role

;

quit;

which produces:

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

All Replies
Super User
Posts: 20,671

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

run;

2. Process data to format you need

data want;

set sample;

response_rate=put(count, 8.)||"("||put(pct_row/100, percent8.)||")";

run;

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,628

## 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

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: 20,671

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

run;

PROC Star
Posts: 7,628

## Re: Struggling with SAS procedure and data

If you just want the printout use:

proc sql;

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,628

## Re: Struggling with SAS procedure and data

You can add whatever format is needed.  E.g.,

proc sql;

as Response_Rate

from have

group by role

;

quit;

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

## Re: Struggling with SAS procedure and data

or, to precisely match your original example:

proc sql;

"%")

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

🔒 This topic is solved and locked.