CODEWITHSUNDEEP
loopssascumulative-sumdo-loops
dhruva_04
dhruva_04

Reputation: 141

SAS Cumulative sum in a do until loop

Hi I want to create a cumulative sum using group by on a variable, but I want to nest it in a do until loop. But I'm not able to get the right value of sum.

I want to increase calls incrementally from 1 to 8(being max) till the condition in "do until" is satisfied. Basically I want to know the total added calls(cumulative) and then compare it with the condition

DATA DAS.UNDER_2;
SET UNDER_1;
BY TERR;
  DO i = 1 TO 8 UNTIL (CUMM >= (285 - ADJUSTED_WL));

  CALLS = i;

  IF FIRST.TERR THEN CUMM = CALLS;
  ELSE CUMM + CALLS;

  IF (CUMM > (285 - ADJUSTED_WL)) THEN  CALLS = 0;
 END;

This is the result I'm getting:

+------+-------------+-------+------+
| TERR | ADJUSTED_WL | CALLS | CUMM |
+------+-------------+-------+------+
| A    |          10 |     2 |    2 |
| A    |          10 |     2 |    5 |
| A    |          10 |     2 |    8 |
| B    |          20 |     2 |    2 |
| B    |          20 |     2 |    5 |
| B    |          20 |     2 |    8 |
| C    |          30 |     2 |    2 |
| C    |          30 |     2 |    5 | 
| C    |          30 |     2 |    8 |
+------+-------------+-------+------+

Whereas, the CUMM should be cumulative sum of the Calls column like 2, 4, 6, 8..

Upvotes: 0

Views: 1711

Answers (1)

Thogerar
Thogerar

Reputation: 339

I found easily an example here : https://communities.sas.com/t5/SAS-Procedures/How-do-I-create-a-cumulative-total-column-subset-by-another/td-p/91118

Data input :

data have;
input ID  AMT;
cards;
10 150
10 100
25 150
25 150
25 150
30 600
30 300
run;

With another column sumbyid:

data want;
  set have;
  by id notsorted;
  if first.id then sumbyid=0;
    sumbyid+amt;
run;

Result:

                    Obs    ID    AMT    sumbyid


                     1     10    150      150

                     2     10    100      250

                     3     25    150      150

                     4     25    150      300

                     5     25    150      450

                     6     30    600      600

                     7     30    300      900

Regards

Upvotes: 1

Related Questions