topic Re: Cumulative Sum by 2 Groups in SAS Procedures

Cumulative Sum by 2 Groups

jammy — Mon, 07 Jul 2014 09:15:07 GMT

Hi,

I want to calculate the cumulative sum but I need to group the data by 2 variables, e.g:

Area	Month	Value	CumValue
A	1	5	5
A	1	5	10
A	2	15	15
A	2	20	35
B	1	10	10
C	1	5	5
D	1	10	10
D	2	5	5
D	3	5	10

my code:

proc sort data= test;

by area month;

run;

data test_cum;

set test;

by area month;

retain cumvalue;

if first.area and first.month then cumvalue= value;

else cumvalue=value+cumvalue;

run;

This will only group by the "area". How do I group it by both "area" & "month"?

Thanks

Re: Cumulative Sum by 2 Groups

RW9 — Mon, 07 Jul 2014 09:19:05 GMT

Change it to;
if first.month then cumvalue= value;

This will do each subgroup within the first group.

Re: Cumulative Sum by 2 Groups

jammy — Mon, 07 Jul 2014 09:37:22 GMT

Many thanks..... so if you use the last variable the dataset was sorted by.... in the By statement, it will group it in all the preceeding variables e.g:

proc sort data=test;

by A B C D E;

run;

So in the following datastep, I just need:

data test_2;

set test:

by E;

...... this will group the data -> A, B, C, D, E

Re: Cumulative Sum by 2 Groups

RW9 — Mon, 07 Jul 2014 10:04:24 GMT

Yes, if you do the following you will see the grouping.

data have;

attrib a b c d e format=best.;

do I=1 to 10;

do j=1 to 5;

do k=1 to 7;

do l=1 to 6;

do m=1 to 3;

a=i; b=j; c=k; d=l; e=1;

output;

end;

run;

data want;

set have;

by a b c d e;

if first.e or last.e then Tick="Y";

run;