proc sort data=Mbr_Info out=test1;
by Member_ID Main_Category;
run;
proc transpose data=test1 out=test2;
var Category:;
by Member_ID Main_Category;
run;
proc sql;
create table test3 as
select distinct test2.Member_ID, test2.Main_Category, sum(COL1*Weight) as Sum_Category from
test2 left join Mbr_weights
on test2.Main_Category=Mbr_weights.Main_Category and category=_NAME_
group by Member_ID || test2.Main_Category
;
quit;
proc transpose data=test3 out=table4;
id Main_Category;
var Sum_Category;
by Member_ID;
run;