I am not sure if I understood exactly what you wanted, but I took a crack at it. Hope it helps you! Also in your original HAVE dataset, you only had 7 values for 9 variables, so I randomly added 2 values per observation for those 2 variables. I also attached a screenshot of what the report looks like! DATA HAVE;
INPUT patientid drug hf db ccb ace me beta plan$;
datalines;
1 1 0 1 0 1 1 0 A
2 0 1 0 0 0 0 1 B
3 1 1 1 1 1 1 1 A
4 0 1 0 1 0 0 1 C
5 1 1 1 1 1 0 0 A
6 1 0 1 0 1 1 1 A
7 0 1 0 0 0 0 1 C
8 1 1 1 1 1 1 0 B
9 0 1 0 1 0 1 1 A
10 0 1 1 1 1 0 1 A
;
run;
DATA HAVE1;
INPUT var$ std;
datalines;
hf 0.01
db 0.001
ccb 0.03
ace 0.033
me 0.044
beta 0.0981
plan 0.20
;
run;
proc sql;
create table have_sum as
select drug,sum(hf) as hf_sum,sum(db) as db_sum, sum(ccb) as ccb_sum,
sum(ace) as ace_sum, sum(me) as me_sum, sum(beta) as beta_sum
from have
group by drug;
quit;
proc sort data=have out=have_sorted;
by drug;
run;
proc transpose data=have_sum out=have_sum_long prefix=drug;
var hf_sum db_sum ccb_sum ace_sum me_sum beta_sum;
id drug;
run;
proc freq data=have;
tables drug*plan/list outpct missing out=plan;
run;
proc sort data=plan out=plan_sorted;
by plan;
run;
proc transpose data=plan_sorted out=plan1(drop=_name_ _label_) prefix=drug;
id drug;
by plan;
var count;
run;
proc sql;
create table merged1 as
select * from
(select var as var1, drug0, drug0/5 format=percent7.2 as pct0, drug1, drug1/5 format=percent7.2 as pct1, std
from have_sum_long as A full join have1 as B
on scan(A._name_,1,"_")=B.var)
outer union corr
select * from
(select plan as var1, drug0, drug0/5 format=percent7.2 as pct0,drug1, drug1/5 format=percent7.2 as pct1, . as std
from plan1)
;
quit;
proc report data=merged1;
columns var1 drug0 pct0 drug1 pct1 std;
define var1/display 'Measures';
define drug0/display 'Drug=0';
define pct0/display 'Drug=0 (%)';
define drug1/display 'Drug=1';
define pct1/display 'Drug=1 (%)';
define std/display 'STD';
run;
... View more