Compute inverse cumulative sum

[mariapf]

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

[Rick_SAS]

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.

[mariapf]

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;

[Rick_SAS]

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.

[PGStats]

Or you could use proc expand

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