How can i get the final dataset ?

[devarayalu]

Please help in this regard.

In a clinical trial two treatments with 1(n=10) and 2(n=10) with a total of 20 subjects.

Suppose the indput AE dataset is:

subid	trt	SOC	PFT
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

Final dataset should be like this?

Variable	1	2	Total	hlt_indx		type
Gastrointestinal disorders	20	20	20	1		hlt
Constipation	0	10	5	1		llt
Hypoaesthesia oral	10	0	5	1		llt
Paraesthesia oral	10	0	5	1		llt
Abdominal pain upper	0	10	5	1		llt
Eye disorders	30	10	20	2		hlt
Conjunctivitis	0	10	5	2		llt
Ocular hyperaemia	10	0	5	2		llt
Eye swelling	20	0	10	2		llt

Thank you very much in advance

[art297]

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;