topic Re: Cumulative Sum in SAS Programming

Cumulative Sum

scouttier — Wed, 01 Nov 2017 19:44:36 GMT

Hello, I would like my data to look something like this:

Area	Value	CumValue
A	5	45
A	5	45
A	15	45
A	20	45
B	10	10
C	5	5
D	10	25
D	5	25
D	5	25

I currently have:

By area;

if first.area then value = 0;

cumvalue + value;

run;

I've seen code that condenses all of them and spits it out so that I would only have 4 rows (A,B,C,D) but I would like to keep each row and have each equal to the last value.

Thanks!!!

Re: Cumulative Sum

Shmuel — Wed, 01 Nov 2017 21:06:53 GMT

It seems that you want:

data want;

set have;

by area;

retain cumvalue;

if first.area then cumvalue = 0;

cumvalue + value;

if last.area then output;

run;

Re: Cumulative Sum

scouttier — Wed, 01 Nov 2017 20:50:42 GMT

Thanks for the reply!!

Two questions -

Why isn't it - - if first.area then cumvalue = 0; (edit - just realized I have it like that in my op : O)

Also, I believe that code removes duplicates and condenses each area into one. I would like each area to remain and have the final cumulative value.

Re: Cumulative Sum

Reeza — Wed, 01 Nov 2017 20:49:36 GMT

The easiest way is to summarize the data and merge it back in.

This example adds the average, but you can do the same for the sum.

https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

Re: Cumulative Sum

Shmuel — Wed, 01 Nov 2017 21:07:37 GMT

You are right, it should be cumvalue=0.
It was a typo.

Re: Cumulative Sum

mkeintz — Thu, 02 Nov 2017 03:13:59 GMT

You can interleave dataset HAVE with itself, using a BY AREA statement to force the interleaving. Each AREA is read twice, the first time building the cumvalue, and the second time outputting the cumvalue:

data have;
  input area $ value;
datalines;
A 5  
A 5  
A 15  
A 20  
B 10  
C 5  
D 10  
D 5  
D 5  
run;

data want;
  set have (in=firstpass) have;
  by area;
  if first.area then cumvalue=value;
  else if firstpass then cumvalue+value;
  if firstpass=0;
run;

Re: Cumulative Sum

Ksharp — Thu, 02 Nov 2017 13:33:00 GMT


data have;
  input area $ value;
datalines;
A 5  
A 5  
A 15  
A 20  
B 10  
C 5  
D 10  
D 5  
D 5  
;
run;

proc sql;
select *,sum(value) as sum
 from have
  group by area;
quit;

Re: Cumulative Sum

s_lassen — Fri, 03 Nov 2017 08:27:05 GMT

@scouttier:

If you want to do it in one data step, this will work:

data want;
  do until(last.area);
    set have;
    by area;
    cumvalue+value; /* this is a SUM statement */
    end;
  do until(last.area);
    set have;
    by area;
    output;
    end;
  cumvalue=0; /* cumvalue is retained because of SUM statement */
run;

Re: Cumulative Sum

scouttier — Fri, 03 Nov 2017 14:46:33 GMT

Thanks so much - I was trying to merge it back in, but this works so much better!