topic Re: Struggling with SAS procedure and data in Statistical Procedures

Struggling with SAS procedure and data

Nupur20 — Tue, 20 Sep 2011 20:45:19 GMT

Hi I have a data in SAS like this:

Role Answer

A -

A Diabetes

B -

B Diabetes

B -

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%)

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.

Struggling with SAS procedure and data

Reeza — Tue, 20 Sep 2011 21:06:08 GMT

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.

Re: Struggling with SAS procedure and data

Nupur20 — Tue, 20 Sep 2011 21:26:07 GMT

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

Re: Struggling with SAS procedure and data

art297 — Tue, 20 Sep 2011 21:57:25 GMT

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;

Re: Struggling with SAS procedure and data

Reeza — Tue, 20 Sep 2011 22:31:44 GMT

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;

Re: Struggling with SAS procedure and data

Nupur20 — Wed, 21 Sep 2011 00:12:30 GMT

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.

Re: Struggling with SAS procedure and data

art297 — Wed, 21 Sep 2011 00:26:42 GMT

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

Re: Struggling with SAS procedure and data

Nupur20 — Wed, 21 Sep 2011 00:48:39 GMT

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.

Re: Struggling with SAS procedure and data

art297 — Wed, 21 Sep 2011 01:02:03 GMT

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;

Re: Struggling with SAS procedure and data

art297 — Wed, 21 Sep 2011 01:14:17 GMT

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%

Re: Struggling with SAS procedure and data

Nupur20 — Wed, 21 Sep 2011 01:26:32 GMT

Thanks a ton!! You guyz are awesome