BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jammy
Calcite | Level 5


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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

This will do each subgroup within the first group.

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

This will do each subgroup within the first group.

jammy
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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