Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Compute inverse cumulative sum

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 12-21-2020 05:57 AM
(1240 views)

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

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Or you could use proc expand

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

PG

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.