topic Re: Rearranging data in Statistical Procedures

Rearranging data

Nupur20 — Thu, 22 Sep 2011 19:48:16 GMT

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

Re: Rearranging data

DLing — Thu, 22 Sep 2011 20:21:01 GMT

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;

Re: Rearranging data

NickR — Thu, 22 Sep 2011 21:10:05 GMT

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

data_null__ — Thu, 22 Sep 2011 22:25:36 GMT

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;

Re: Rearranging data

Ksharp — Fri, 23 Sep 2011 03:19:21 GMT

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