Contributor
Posts: 33

# rearranging data in sas based on different response

I have a data in sas like:

Category              Response

A                         50% - 70%

A                          0%

A                          0%

A                          More than 70%

B                          More than 70%

B                          1% - 49%

B                            0%

B                             0%

B                           50% -70%

I want my result data to look like:

Response                  A                B

0%                          2 (50%)        2 (40%)

1% -49%                   0(0%)         1 (20%)

50%- 70%                 1 (25%)       1 (20%)

More than 70%         1 (25%)         1 (20%)

Explanation of result data:

There are two categories A (=4)and B (=5) and there are four different types of response 0%, 1% -49%, 50% -70% and more than 70%.

In category A, only two out of four responded 0%, thus result is 2 (50%). Similarly in A, on;y one out of four responded 50-70%, thus result is 1 (25%).

Same as B for each response.

I would be grateful for your time reagarding the same.

Super User
Posts: 10,768

## rearranging data in sas based on different response

For your situation, I prefer to use proc report.

```data temp;
input Category     \$     Response & \$20.;
cards;
A                         50% - 70%
A                          0%
A                          0%
A                          More than 70%
B                          More than 70%
B                          1% - 49%
B                            0%
B                             0%
B                           50% - 70%
;
run;
options missing=0;
proc report data=temp nowd out=x;
column response category,(n pctn cate);
define response/group;
define category/across ' ';
define n/noprint ;
define pctn/ noprint;
define cate/computed ' ';
compute cate /char length=10;
_c4_=cats(_c2_,'(',_c3_*100,'%)');
_c7_=cats(_c5_,'(',_c6_*100,'%)');
endcomp;
run;
```

Ksharp

Frequent Contributor
Posts: 79

## Re: rearranging data in sas based on different response

Thanks to KSharp's idea of using PROC REPORT. Here is one more solution using PROC TABULATE.

PROC FORMAT;

PICTURE MYPCT LOW-HIGH = '009%)'   (PREFIX="(");

RUN;

OPTIONS MISSING=0;

PROC TABULATE DATA = DSN;

CLASS RESPONSE CATEGORY;

TABLE RESPONSE, CATEGORY*(N='' PCTN<RESPONSE>=' ' *F=MYPCT.);

RUN;

Though we are getting the expected output, the 0's are not displaying with % sign. Can some one help on how to display 0 along with % sign.

Thanks

Dhanasekaran R

Super User
Posts: 10,768

## Re: rearranging data in sas based on different response

My code has already achieved that.

Ksharp

Frequent Contributor
Posts: 79

## Re: rearranging data in sas based on different response

Yes I understood your solution worked well. But I just tried the same output with PROC TABULATE except the 0 with % sign everything is working fine.

Can you help how to get this 0 with % sign in my solution.

Thanks

Dhanasekarna R

Super User
Posts: 10,768

## Re: rearranging data in sas based on different response

I am afraid that you need to pre-process your datasets firstly.

That is to say change the missing value into 0 , then use proc tabulate.

Is there someone can use proc tabulate to get it directly?

Ksharp

Super User
Posts: 10,768

## Re: rearranging data in sas based on different response

It is very interesting. If I used O to instead of zero ,it worked. If you don't mind this.

```data temp;
input Category     \$     Response & \$20.;
cards;
A                         50% - 70%
A                          0%
A                          0%
A                          More than 70%
B                          More than 70%
B                          1% - 49%
B                            0%
B                             0%
B                           50% - 70%
;
run;

PROC FORMAT;

PICTURE MYPCT LOW-HIGH = '009%)'   (PREFIX="(")
. = '(O%)';

RUN;

options missing=O;
PROC TABULATE DATA = temp ;

CLASS RESPONSE CATEGORY;

TABLE RESPONSE, CATEGORY*(N='' PCTN=' ' *F=MYPCT.);

RUN;
```

Ksharp

Frequent Contributor
Posts: 79

## rearranging data in sas based on different response

Yes I have got O in place of zero. Thanks Ksharp for your idea.   But that is not an accepted soultion. . Does some one have any idea ?

Thanks

Dhanasekaran R

Discussion stats
• 7 replies
• 204 views
• 0 likes
• 3 in conversation