Calcite | Level 5

## Rearranging data

Hi I have a data in sas like:

Role                      A                  B                     C                                 D                 E

IT                       Bone            Diabetes       haemoglobin                  -                 Fibrin

Doctor                Bone               -                Haemoglobin               Blood             -

Doctor                   -               Diabetes        haemoglobin               Blood            Fibrin

Clinic                  Bone             -                          -                        Blood            Fibrin

Clinic                  Bone               -                Haemoglobin               Blood

How can I rearrange data to get like this:

IT                           Doctor                          Clinic

Bone                      1 (100%)                 1 (50%)                      2 (100%)

Diabetes                 1 (100%)                 1 (50%)                      0 (0%)

Haemoglobin           1 (100%)                 2 (100%)                    1 (50%)

Blood                      0 (0%)                     2 (100%)                    2 (100%)

Firbin                      1 (100%)                 1 (50%)                      1 (50%)

Let me explain my result data:

take each role separate: for eg, there is only one IT. For A, the IT answered Bone, thus the response rate among IT woule be 100%.

Since IT didnt answer D, therefore the response rate is 0%.

Similarly, take doctor as an example. There are two doctors and only one answered A, so the response rate would be 50%. Both doctors answered C, therefore the response would be 100%.

I know its a bit complex but your suggestions would really help me.

Thanks a ton!!

4 REPLIES 4
Obsidian | Level 7

## Re: Rearranging data

data in;
length role a b c d e \$ 12;
infile cards truncover;
input Role a b c d e;

Bone = ( a = 'Bone' );                    * modify to remove case sensitivity if you want;
Diabetes = ( b = 'Diabetes' );
Haemoglobin = ( c = 'haemoglobin' );
Blood = ( d = 'Blood' );
Fibrin = ( e = 'Fibrin' );

drop a b c d e;

cards;
IT        Bone      Diabetes       haemoglobin      -                Fibrin
Doctor    Bone         -           haemoglobin      Blood            -
Doctor    -         Diabetes       haemoglobin      Blood            Fibrin
Clinic    Bone         -             -              Blood            Fibrin
Clinic    Bone         -           haemoglobin      Blood
;

proc summary data=in missing nway;
class role;
var Bone Diabetes Haemoglobin Blood Fibrin;
output out=temp(drop=_:) sum=;
run;

proc transpose data=temp out=want name=Type;
id role;
run;

Quartz | Level 8

## Re: Rearranging data

data one;

length Role A B C D E \$20;

infile datalines dlm=',';

input Role A B C D E \$;

datalines;

IT,Bone,Diabetes,haemoglobin, ,Fibrin

Doctor,Bone, ,Haemoglobin,Blood,

Doctor, ,Diabetes,haemoglobin,Blood,Fibrin

Clinic,Bone, , ,Blood,Fibrin

Clinic,Bone, ,Haemoglobin,Blood,

;

run;

proc sql;

create table two as select count(a) as Bone, count(b) as Diabetes,count(c) as Haemoglobin,

count(d) as Blood,count(e) as Fibrin,count(*) as total,role from one group by role;

quit;

data two;

set two;

call symput(role,strip(put(total,best.)));

drop total;

run;

proc transpose data=two out=three name=Answer prefix=_;

id role;

run;

data final;

set three;

length It Doctor Clinic \$20;

Clinic = strip(put(_clinic,best.))||'('||strip(put((_clinic/&clinic)*100,best.))||'%)';

doctor = strip(put(_doctor,best.))||'('||strip(put((_doctor/&doctor)*100,best.))||'%)';

It = strip(put(_it,best.))||'('||strip(put((_it/&it)*100,best.))||'%)';

drop _:;

run;

proc print noobs; run;

## Re: Rearranging data

I think I've got this right.  To me you want a method that doesn't require that you know the values of the variables.

`data disease;   infile cards missover;   input (Role a b c d e)(:\$12.);   id + 1;   cards;IT      Bone   Diabetes haemoglobin   .     FibrinDoctor  Bone     .      haemoglobin   Blood .   Doctor  .      Diabetes haemoglobin   Blood Fibrin Clinic  Bone     .      .             Blood FibrinClinic  Bone     .      haemoglobin   Blood           ;;;;   run;proc transpose out=t1(rename=(col1=Disease));   by id role;   var a--e;   run;proc sort;   by _name_;   run;proc freq;   by _name_;   tables disease * role / nopercent norow missing;   run;`
Super User

## Re: Rearranging data

```data disease;
infile cards missover;
input (Role a b c d e)(:\$12.);
cards;
IT      Bone   Diabetes haemoglobin   .     Fibrin
Doctor  Bone     .      haemoglobin   Blood .
Doctor  .      Diabetes haemoglobin   Blood Fibrin
Clinic  Bone     .      .             Blood Fibrin
Clinic  Bone     .      haemoglobin   Blood
;;;;
run;
proc sql noprint;
create table have as
select role,cats(count(a),'(',put(count(a)/count(*),percent8.),')') as bone ,
cats(count(b),'(',put(count(b)/count(*),percent8.),')') as diabetes,
cats(count(c),'(',put(count(c)/count(*),percent8.),')') as haemoglobin,
cats(count(d),'(',put(count(d)/count(*),percent8.),')') as blood,
cats(count(e),'(',put(count(e)/count(*),percent8.),')') as fibrin
from disease
group by role;
quit;
proc transpose data=have out=want;
id role ;
var bone diabetes haemoglobin blood fibrin;
run;
```

Ksharp

Discussion stats
• 4 replies
• 1386 views
• 0 likes
• 5 in conversation