topic Re: Sum across selective rows in SAS Programming

Sum across selective rows

wagh — Fri, 14 Dec 2018 02:15:51 GMT

Hi, I have a summarized dataset which conatins 6 different products by their monthly sales. These 6 products belong to two different groups. ABC is group 1 and DEF belong to group2. It looks like this.

Name	Month1	Month2	Month3
A	10	10	10
B	20	20	40
C	40	30	20
D	30	40	60
E	10	50	50
F	20	60	30

I need to add rows to the data which sum up sales of all products in that group. It should look like this:

Name	Month1	Month2	Month3
A	10	10	10
B	20	20	40
C	40	30	20
SUM_ABC	70	60	70
D	30	40	60
E	10	50	50
F	20	60	30
SUM_DEF	60	150	140

Could anyone please tell me how should it be done? Thank you.

Re: Sum across selective rows

r_behata — Fri, 14 Dec 2018 02:44:46 GMT

Assumption made : Groups are pre-existing.

data have;
length name $10.;
input Grp $ Name	$ Month1	Month2	Month3;

cards;
1 A 10 10 10
1 B 20 20 40
1 C 40 30 20
2 D 30 40 60
2 E 10 50 50
2 F 20 60 30
;
run;

data temp(rename=(Month1_=Month1 Month2_=Month2 Month3_=Month3 name_=name));
length name_ $10.;
Month1_=0;
Month2_=0;
Month3_=0;

name_=' ';

 do until(last.grp);
	set have;
	by grp;
	Month1_=sum(Month1_,Month1);
	Month2_=sum(Month2_,Month2);
	Month3_=sum(Month3_,Month3);
	name_=cats(name_,name);
	
end;
name_=cats("SUM_",NAME_);

drop Month1 Month2 Month3 name;
run;

data want;
	set have temp;
	by grp;
	drop grp;
run;

Re: Sum across selective rows

Astounding — Fri, 14 Dec 2018 13:51:56 GMT

Here's a better, more flexible goal. Add a fifth variable, GROUP, that will be 1 when NAME is A, B, or C. GROUP will be 2 when NAME is D, E, or F.

This will easily let you create the report you want, and adds to your data the relationship between a NAME and a GROUP.