Help using Base SAS procedures

Cumulative Sum by 2 Groups

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

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
Super User
Posts: 7,955

Re: Cumulative Sum by 2 Groups

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

This will do each subgroup within the first group.

View solution in original post


All Replies
Solution
‎07-07-2014 05:19 AM
Super User
Super User
Posts: 7,955

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
Super User
Posts: 7,955

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 3288 views
  • 0 likes
  • 2 in conversation