topic Re: Compute inverse cumulative sum in SAS Programming

Compute inverse cumulative sum

mariapf — Mon, 21 Dec 2020 10:57:28 GMT

Hi all,

I hope this is a quick question to answer. I need to compute an inverse cumulative sum in SAS. My output should look like this:

UNITS UOC DATE GROUP

199998 396333 31/01/2020 1

176000 196335 29/02/2020 1

1523,38 20335 31/03/2020 1

12939,91 18811 30/04/2020 1

5871,59 5871,59 31/05/2020 1

99999 195984 31/01/2020 2

88174,6 95985 29/02/2020 2

764,716 7811 31/03/2020 2

6508,54 7046 30/04/2020 2

537,59 537,59 31/05/2020 2

Therefore, for row i in UOC I need the sum of units from i to n. The additional difficulty of this is that I have several groups (1, 2..) and I have to sum by group.

I know how to compute a regular cumulative sum using a retain statement, and I have thought of reversing the order of the data, creating the variable UOC by computing a regular cumulative sum and then reversing it again. But it may be inefficient if I were to deal with a larger dataset and I don't know if this can work properly having to sum by group. I've also been looking into programming a summation but I am not sure it is a good solution either.

Thank you so much for your time. Any help is appreciated

Re: Compute inverse cumulative sum

Rick_SAS — Mon, 21 Dec 2020 11:51:58 GMT

I think that your suggestion to sort the data in decreasing order is correct. You can keep Group in increasing order (so you can use BY-group processing) but sort the Date variable in descending order:

proc sort data=Have out=Want;
by Group  
   descending Date;
run;

You can then use the usual BY GROUP analysis to compute the cumulative sums in reverse chronological order for each group.

Re: Compute inverse cumulative sum

mariapf — Mon, 21 Dec 2020 12:39:50 GMT

Hi Rick, thank you so much for your input!!

I have tried this code, sorting in descending order and then reordering back and it seems to work.

proc sort data=work.step5 out=work.sum_test;
by GROUP 
   descending DATE;
run;

data work.sum_test_2;
set work.sum_test;
	by GROUP;
		retain uoc;
		if first.GROUP then uoc = units;
		else uoc = uoc + units;
run;

proc sort data=work.sum_test_2 out=work.sum_test_3;
by GROUP DATE;
run;

Re: Compute inverse cumulative sum

Rick_SAS — Mon, 21 Dec 2020 14:01:23 GMT

Yes. And depending on your application, you might not need the second sort.

Your IF-THEN/ELSE logic is correct, but I would have written it as

if first.GROUP then uoc = 0;
uoc + units;

Either way works and gives the same answer.

Re: Compute inverse cumulative sum

PGStats — Mon, 21 Dec 2020 16:57:32 GMT

Or you could use proc expand

proc expand data=have out=want;
by group;
convert units=uoc / transformout=(reverse cusum reverse);
run;