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!!
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;
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;
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;
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 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.