Need to calculate sub total,total and to generate serial no. column for the group variable

[vidyasagar1]

data one;
 infile datalines;
 input Group1$ Head :$20. Reported;
 datalines;
 Adult Drink 2 
 Adult Commit 2
 Adult Medicine 1
 Adult Grains 5
 Adult Others 1
 Burglary Banks 1
 Burglary CommercialPlaces 3
 Burglary Residential 5
 Burglary Others 6
 Hurt Attack 4
 Hurt Disputes 3
 Hurt Others 7
 ;
RUN;

 
proc report data=one;
 column group1 head Reported;
 define group1 / group;
 define head / group;
run;

I have data like above and i need to get the output like below using proc report

Sl. No.	Heads of Group	Reported
1	Adult
	Commit	2
	Drink	2
	Grains	5
	Medicine	1
	Others	1
	Sub Total	11
2	Burglary
	Banks	1
	Commercial	3
	Others	6
	Residentia	5
	Sub Total	15
3	Hurt
	Attack	4
	Disputes	3
	Others	7
	Sub Total	14
	Total	40

[andreas_lds · Accepted solution]

Welcome in the community!

Adding break and rbreak statements will give you sub-total and total. Generating the serial number could be done in a compute-block or by applying a self-defined format. But i have no idea how to move the value of Group1 into Head-column without modifying dataset one.

The format could be created with the following code:

data Group1Fmt;
   set one(keep= Group1 rename=(Group1=Start));
   by Start;
   
   length FmtName $ 32 Type $ 1;
   retain FmtName 'GroupFmt' Type 'C';
   
   if first.Start then do;
      Label + 1;      
      output;
   end;
run;

proc format cntlin=Group1Fmt;
run;

The format is applied in proc report

proc report data=one;
 column group1 head Reported;
 define group1 / group format=$groupfmt.;
 define head / group;
 
 break after group1 / summarize;
 rbreak after / summarize;
run;

[vidyasagar1]

Thank you Andrea for your reply and it is helpful and also can you please suggest how to modify data set one to get the output as required.