BookmarkSubscribeRSS Feed
Nupur20
Calcite | Level 5

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
DLing
Obsidian | Level 7

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
Quartz | Level 8

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__
Jade | Level 19

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
Super User

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

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