Please try the below code
data have;
input STUDY$ ID$ d0101 d0102 d0103;
cards;
study1 person1 . . 0
study1 person2 0 0 0
study1 person3 0 0 1
study1 person4 1 1 1
study1 person5 . . 1
study1 person6 0 0 1
study1 person7 1 1 .
study1 person8 1 1 0
study1 person9 1 0 1
;
proc sql noprint;
select name, count(name) into: vars separated by '|', :cnt from dictionary.columns where libname='WORK' and memname='HAVE' and name like 'd%';
quit;
%put &vars &cnt;
options mprint;
%macro test;
proc sql;
create table want as select study,
%do i = 1 %to &cnt;
sum(%scan(&vars, &i, "|")=1) as %scan(&vars, &i, "|"),
%end;
'Pass' as status from have
union
select study,
%do i = 1 %to &cnt;
sum(%scan(&vars, &i, "|") ne .) as %scan(&vars, &i, "|"),
%end;
'Total' as status from have
union
select study,
%do i = 1 %to &cnt;
(sum(%scan(&vars, &i, "|") =1)/sum(%scan(&vars, &i, "|") ne .))*100 as %scan(&vars, &i, "|") ,
%end;
'Percent Pass' as status from have group by study;
quit;
%mend;
%test;
... View more
