Rearranging data

[Nupur20]

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

[DLing]

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;

[NickR]

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;

[data_null__]

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   .     Fibrin
Doctor  Bone     .      haemoglobin   Blood .   
Doctor  .      Diabetes haemoglobin   Blood Fibrin 
Clinic  Bone     .      .             Blood Fibrin
Clinic  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;

[Ksharp]

How about:

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