topic Re: Sum a column - Stop and Restart when a certain value is achieved in SAS Programming

Sum a column - Stop and Restart when a certain value is achieved

CHELS — Thu, 15 Jun 2023 20:14:14 GMT

I have a data that looks like this:

ID	Count
1	3
2	1
3	2
4	12
5	3
6	2
7	9
8	11
9	3
10	7

I want to sum up 'Count' starting from the first row. As soon as the Sum >= 20, the process starts over. Something looks like this:

ID	Count	Sum
1	3	3
2	1	4
3	2	6
4	12	18
5	3	21
6	2	2
7	9	11
8	11	22
9	3	3
10	7	10

Re: Sum a column - Stop and Restart when a certain value is achieved

PaigeMiller — Thu, 15 Jun 2023 20:19:25 GMT

data want;
    set have;
    if sum>=20 then sum=0;
    sum+count;
run;

Re: Sum a column - Stop and Restart when a certain value is achieved

ballardw — Thu, 15 Jun 2023 20:22:21 GMT

One way:

data have;
  input ID 	Count;
datalines;
1 	3
2 	1
3 	2
4 	12
5 	3
6 	2
7 	9
8 	11
9 	3
10 	7
;

data want;
   set have;
   retain runningsum;
   runningsum+count;
   output;
   if runningsum ge 20 then runningsum=0;
run;

Please note the use of a data step to provide example data. It avoids us having to ask lots of questions in some cases.

Retain names a variable that will hold the value of a variable across iterations of the data step.

The "trick" here is to output the values and if you have written one greater than or equal to your target set it to 0 afterwards before used in the next total.

I don't like using variable names like Sum, which happens to be function, as it may confuse code and doesn't say anything about the sum of what.

Re: Sum a column - Stop and Restart when a certain value is achieved

CHELS — Fri, 16 Jun 2023 16:26:35 GMT

THANK YOU!!