topic Re: Sub-Group Total in SAS Programming

Sub-Group Total

Swati24 — Mon, 10 Dec 2018 06:03:02 GMT

I have a data like this :

data one;
  infile datalines;
  input Group_Name: $12. Head_Name :$12. LastYear_Count BeforeLastYear_Count;
 datalines;
R&D IT 1 2
R&D Testing 0 5
R&D Production 4 0
R&D Development 1 0
Sales Book 5 4
Sales Product 10 0
Sales Kit 1 1
Admin Marketing 4 1
Admin HR 5 2
Admin Project 6 3
;
run;

I need the output like this:

Group_Name	Head_Name	LastYear_Count	BeforeLastYear_Count
R&D	IT	1	2
R&D	Testing	0	5
R&D	Production	4	0
R&D	Development	1	0
SubTotal		6	7
Sales	Book	5	4
Sales	Product	10	0
Sales	Kit	1	1
SubTotal		16	5
Admin	Marketing	4	1
Admin	HR	5	2
Admin	Project	6	3
SubTotal		15	6

Re: Sub-Group Total

Jagadishkatam — Mon, 10 Dec 2018 06:26:42 GMT

data one;
  infile datalines;
  input Group_Name: $12. Head_Name :$12. LastYear_Count BeforeLastYear_Count;
 datalines;
R&D IT 1 2
R&D Testing 0 5
R&D Production 4 0
R&D Development 1 0
Sales Book 5 4
Sales Product 10 0
Sales Kit 1 1
Admin Marketing 4 1
Admin HR 5 2
Admin Project 6 3
;
run;

proc sort data=one;
by Group_Name;
run;

data want;
set one;
by Group_Name;
output;
retain LastYear_Count_ BeforeLastYear_Count_;
if first.Group_Name then do; LastYear_Count_=.;BeforeLastYear_Count_=.;end;
LastYear_Count_+LastYear_Count;
BeforeLastYear_Count_+BeforeLastYear_Count;
Group_Name='Sub Total';
if last.Group_Name then do;LastYear_Count=LastYear_Count_;BeforeLastYear_Count=BeforeLastYear_Count_;
output;
end;
drop BeforeLastYear_Count_ LastYear_Count_;
run;

Re: Sub-Group Total

Swati24 — Mon, 10 Dec 2018 06:57:15 GMT

Thanks but this will not work as after each group value one more row should be added that is subtotal.

Re: Sub-Group Total

learsaas — Mon, 10 Dec 2018 07:10:13 GMT

Re: Sub-Group Total

Swati24 — Mon, 10 Dec 2018 07:25:16 GMT

Thanks I got the solution by using below Proc Report code:
proc report data=one;
column group_name head_name Before_Last_Year Last_Year;
define group_name / group;
define head_name / group;
define Before_Last_Year / sum;
define Last_Year / sum;
break after group_name / summarize style={background=lightyellow};
rbreak after / summarize style=Header;
compute after group_name;
group_name = 'Sub Total';
endcomp;
run;

Re: Sub-Group Total

Jagadishkatam — Mon, 10 Dec 2018 07:40:24 GMT

I believe it is giving the expected output the only update is with regard to making Head_Name='';

could you please check now and let me know.

I know you got the solution through proc report but would like to know if this also works.

data want;
set one;
by Group_Name;
output;
retain LastYear_Count_ BeforeLastYear_Count_;
if first.Group_Name then do; LastYear_Count_=.;BeforeLastYear_Count_=.;end;
LastYear_Count_+LastYear_Count;
BeforeLastYear_Count_+BeforeLastYear_Count;
Group_Name='Sub Total';
Head_Name='';
if last.Group_Name then do;LastYear_Count=LastYear_Count_;BeforeLastYear_Count=BeforeLastYear_Count_;
output;
end;
drop BeforeLastYear_Count_ LastYear_Count_;
run;

Re: Sub-Group Total

RW9 — Mon, 10 Dec 2018 08:55:10 GMT

Please use the search functionality before posting, there are hundreds of posts already on this topic. This one from last week for instance:

https://communities.sas.com/t5/SAS-Programming/insert-a-row-at-the-end-of-specific-rows-with-Totals/m-p/518000#M140142

Re: Sub-Group Total

singhsahab — Mon, 10 Dec 2018 10:44:50 GMT

Hi ,

data one;
  infile datalines;
  input Group_Name: $12. Head_Name :$12. LastYear_Count BeforeLastYear_Count;
 datalines;
R&D IT 1 2
R&D Testing 0 5
R&D Production 4 0
R&D Development 1 0
Sales Book 5 4
Sales Product 10 0
Sales Kit 1 1
Admin Marketing 4 1
Admin HR 5 2
Admin Project 6 3
;
run;

proc report data=one out=want1;
column  group_name Head_Name LastYear_Count BeforeLastYear_Count;
define group_name/order;
define head_name/display;
define LastYear_Count/sum;
define BeforeLastYear_Count/sum;
break after group_name/summarize;
run;

data final (drop=_break_);
set want1;
by group_name ;
if last.group_name then group_name='totel';
run;

Thanks 🙂