Programming the statistical procedures from SAS

Rearranging data

Reply
Contributor
Posts: 33

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

Frequent Contributor
Posts: 104

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=_Smiley Happy sum=;
run;

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

Frequent Contributor
Posts: 81

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;

Respected Advisor
Posts: 3,780

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   .     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;

Super User
Posts: 9,766

Re: Rearranging data

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

Ask a Question
Discussion stats
  • 4 replies
  • 139 views
  • 0 likes
  • 5 in conversation