Cumulative Sum by 2 Groups

[jammy]

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

[RW9 · Accepted solution]

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

This will do each subgroup within the first group.

[jammy]

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

[RW9]

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;