## Cumulative Sum by 2 Groups

Solved
Occasional Contributor
Posts: 15

# Cumulative Sum by 2 Groups

Hi,

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

AreaMonthValueCumValue
A155
A1510
A21515
A22035
B11010
C155
D11010
D255
D3510

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

Accepted Solutions
Solution
‎07-07-2014 05:19 AM
Super User
Posts: 9,617

## Re: Cumulative Sum by 2 Groups

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

This will do each subgroup within the first group.

All Replies
Solution
‎07-07-2014 05:19 AM
Super User
Posts: 9,617

## Re: Cumulative Sum by 2 Groups

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

This will do each subgroup within the first group.

Occasional Contributor
Posts: 15

## Re: Cumulative Sum by 2 Groups

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

Super User
Posts: 9,617

## Re: Cumulative Sum by 2 Groups

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;

end;

end;

end;

end;

run;

data want;

set have;

by a b c d e;

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

run;

🔒 This topic is solved and locked.