Seems simple enough.
data want;
do until(last.date);
set have;
by date;
array cat[7];
array top[7];
array bottom[7];
array rate[7];
do index=1 to dim(cat);
if cat[index] then do;
top[index]=sum(top[index],value);
bottom[index]=sum(bottom[index],1);
end;
end;
end;
do index=1 to dim(cat);
rate[index]=divide(top[index],bottom[index]);
end;
drop index cat: top: bottom: value;
run;
Obs date rate1 rate2 rate3 rate4 rate5 rate6 rate7
1 2021-01-18 0.750 0.50000 0.5 0.75 0.66667 0.57143 0.71429
2 2021-02-18 0.600 0.20000 0.5 0.25 0.50000 0.50000 0.50000
3 2021-03-18 0.625 0.41667 0.5 0.40 0.57143 0.50000 0.50000
But it would be much easier with a different structure where the category number is not stored in the NAME of a variable but instead as the VALUE of a variable instead.
data have ;
input date :date. value @ ;
do cat=1 to 7;
input result @;
output;
end;
format date yymmdd10.;
DATALINES;
18-Jan-2021 0 1 0 0 0 1 1 0 0
18-Jan-2021 0 0 1 1 0 0 1 1 0
18-Jan-2021 1 0 1 0 1 0 1 1 1
18-Jan-2021 0 0 1 1 0 0 1 0 0
18-Jan-2021 1 1 0 0 0 0 0 0 1
18-Jan-2021 0 0 1 0 1 0 0 1 0
18-Jan-2021 1 0 1 0 1 1 0 1 1
18-Jan-2021 1 0 0 1 0 0 1 1 1
18-Jan-2021 1 1 1 1 0 0 0 1 1
18-Jan-2021 1 1 0 0 0 1 1 0 1
18-Jan-2021 1 0 0 0 1 0 1 1 1
18-Feb-2021 0 1 1 1 0 1 0 1 0
18-Feb-2021 0 0 1 0 1 0 0 0 0
18-Feb-2021 1 1 0 1 0 0 1 0 1
18-Feb-2021 1 0 0 1 0 1 1 0 1
18-Feb-2021 1 1 1 0 0 1 0 1 1
18-Feb-2021 1 1 0 0 1 0 0 1 1
18-Feb-2021 0 0 1 1 1 0 1 0 0
18-Feb-2021 0 1 1 0 1 1 1 1 0
18-Mar-2021 0 0 1 0 1 1 0 0 0
18-Mar-2021 0 1 1 0 1 0 1 0 0
18-Mar-2021 1 1 1 1 0 0 1 0 1
18-Mar-2021 0 0 1 0 0 0 1 1 0
18-Mar-2021 1 1 1 1 0 1 1 0 1
18-Mar-2021 1 1 1 1 1 1 0 1 1
18-Mar-2021 0 1 0 0 1 0 1 0 0
18-Mar-2021 0 1 1 0 0 0 0 0 0
18-Mar-2021 1 1 1 1 1 1 1 0 1
18-Mar-2021 1 0 1 0 1 1 1 1 1
18-Mar-2021 0 0 1 1 0 0 0 0 0
18-Mar-2021 0 0 1 1 1 0 0 0 0
18-Mar-2021 0 0 1 1 1 1 1 1 0
18-Mar-2021 0 0 0 1 1 1 1 1 0
18-Mar-2021 1 1 0 0 1 0 1 1 1
;
proc sql ;
create table want as
select date,cat
, sum(result*value)/sum(result) as rate
from have
group by 1,2
;
quit;
Which you could then use to make a REPORT that looks like your request.
proc report data=want;
column date rate,cat ;
define date/group;
define rate/sum ' ';
define cat/across ' ';
run;
date 1 2 3 4 5 6 7
2021-01-18 0.75 0.5 0.5 0.75 0.6666667 0.5714286 0.7142857
2021-02-18 0.6 0.2 0.5 0.25 0.5 0.5 0.5
2021-03-18 0.625 0.4166667 0.5 0.4 0.5714286 0.5 0.5
... View more
