I don't have time to optimize the following code, but it appears to produce your wanted result: data have; informat subid $3.; informat soc $30.; informat pft $30.; input subid trt SOC & PFT &; cards; 021 2 Gastrointestinal disorders Constipation 031 1 Gastrointestinal disorders Hypoaesthesia oral 065 1 Gastrointestinal disorders Paraesthesia oral 103 2 Gastrointestinal disorders Abdominal pain upper 020 2 Eye disorders Conjunctivitis 087 1 Eye disorders Ocular hyperaemia 087 1 Eye disorders Eye swelling 088 1 Eye disorders Eye swelling ; data want (keep=variable _: total hlt_indx type); set have; by soc pft notsorted; retain _1 _2 grand_total grand_1 grand_2 type; if first.soc then do; type='lit'; grand_total=0; grand_1=0; grand_2=0; hlt_indx+1; end; if first.pft then do; _1=0; _2=0; end; _1=ifn(trt=1,sum(_1,10),sum(_1,0)); _2=ifn(trt=2,sum(_2,10),sum(_2,0)); if last.pft then do; total=sum(_1,_2)/2; grand_total=sum(grand_total,total); grand_1=sum(grand_1,_1); grand_2=sum(grand_2,_2); variable=pft; output; end; if last.soc then do; type='hlt'; total=grand_total; _1=grand_1; _2=grand_2; variable=soc; output; end; run; proc sort data=want; by hlt_indx type; run; data want; retain variable _1 _2 Total hlt_indx type; set want; run;
... View more