topic Re: Cumulative sum in New SAS User

Cumulative sum

dac_js — Sun, 26 Jun 2022 02:21:18 GMT

I have data in following format:

data have;
input
id var1 var2;
datalines;
1 0 0
1 1 0
1 1 0
1 0 0
2 1 1
2 1 0
2 0 0
2 1 1
2 1 0
;
run;

Trying to create cumulative value for different variables (var1 and var2 here) based on IDs. I am expecting something like this:

Obs	id	var1	var2
1	1	0	0
2	1	1	0
3	1	2	0
4	1	2	0
5	2	1	1
6	2	2	1
7	2	2	1
8	2	3	2
9	2	4	2

Re: Cumulative sum

ballardw — Sun, 26 Jun 2022 06:27:27 GMT

Like this? If you try to place the cumulative totals into the same named variable you add a great deal of complexity for no real gain.

data have;
input
id var1 var2;
datalines;
1 0 0
1 1 0
1 1 0
1 0 0
2 1 1
2 1 0
2 0 0
2 1 1
2 1 0
;

data want;
  set have;
  by id;
  retain cumvar1 cumvar2;
  if first.id then call missing(cumvar1,cumvar2);
  cumvar1+var1;
  cumvar2+var2;
run;

Retain sets of variables that will keep values across iterations of the data step boundary, such as your cumulative totals.

Use of By means that SAS will create automatic variables First. and Last. for each variable on the By statement that take values of 1 (or True) and 0 (or False) that indicate whether the current record is one of the boundaries, first or last, or not. So this tests if the current record is the first for an Id group. If so, then the Call Missing function sets the retained values to missing so the total is not carried into the new Id group. The Cumvar1+var1 means "add the current value of Var1 to the cumvar1.

Re: Cumulative sum

Kurt_Bremser — Sun, 26 Jun 2022 06:31:16 GMT

Use new, retained variables:

data want;
set have (rename=(var1=_var1 var2=_var2));
by id;
if first.id
then do;
  var1 = _var1;
  var2 = _var2;
end;
else do;
  var1 + _var1;
  var2 + _var2;
end;
drop _:;
run;

Use of a Sum Statement causes an implicit RETAIN..