topic Follow up Re: Accumulating totals in a series in SAS Programming

Accumulating totals in a series

Tami99 — Fri, 07 Feb 2020 22:02:09 GMT

I have a data series as follows. I'd like to come up with the sum for column A for each continuous series of C for each ID. For instance, the first continuous series in C for ID 101, the sum in A is 5; in the next continuous series in C for ID 101, the sum in A is 4. I cannot use proc sql to get the sum because I cannot group by ID and C as the number in C is not unique.My intuition is that I probably need some retain statement along with a loop to accomplish this. But I'm not sure how to make it work. I'd appreciate any help in coding it. Thanks!

ID	A	B	C
101	1	0	6
101	1	1	6
101	1	1	6
101	0	1	6
101	1	0	6
101	1	1	6
101	0	0	0
101	1	1	6
101	1	0	6
101	0	1	6
101	0	1	6
101	1	0	6
101	1	0	6
102	0	1	5
102	1	1	5
102	1	1	5
102	0	1	5
102	1	0	5

Re: Accumulating totals in a series

ChrisNZ — Fri, 07 Feb 2020 22:26:58 GMT

Like this?

data WANT; 
  set HAVE;
  by ID C notsorted;
  SUMA+A;
  if last.C then do;
    output;
    SUMA=0;
  end
run;

Re: Accumulating totals in a series

Tami99 — Fri, 07 Feb 2020 22:42:39 GMT

Thank you for your quick reply, Chris. But I have a question. In your code, the last.c refers to the last observation in column C for an ID (obs #13), or the last observation in Column C in a continuous series where C is not zero (obs #6). I'd like to accumulate A by the latter. Thanks!

obs	ID	A	B	C
1	101	1	0	6
2	101	1	1	6
3	101	1	1	6
4	101	0	1	6
5	101	1	0	6
6	101	1	1	6
7	101	0	0	0
8	101	1	1	6
9	101	1	0	6
10	101	0	1	6
11	101	0	1	6
12	101	1	0	6
13	101	1	0	6
14	102	0	1	5
15	102	1	1	5
16	102	1	1	5
17	102	0	1	5
18	102	1	0	5

Re: Accumulating totals in a series

PGStats — Fri, 07 Feb 2020 23:31:55 GMT

All you might need is to replace

output;

with

if C ne 0 then output;

in @ChrisNZ's code above.

Otherwise, it does exactly what you want.

Re: Accumulating totals in a series

ChrisNZ — Sat, 08 Feb 2020 01:32:10 GMT

Unsure I understand. Please provide the desired utput.

Maybe don't read the observations where C equals 0?

where C;

Re: Accumulating totals in a series

Tami99 — Sat, 08 Feb 2020 01:53:48 GMT

The change that PGStats suggested works! Thank you both!!!

Follow up Re: Accumulating totals in a series

Tami99 — Thu, 13 Feb 2020 16:44:35 GMT

I have a follow up programming question. With the codes above, I did get the output for each cumulative total of column A in a series. However, is there any way to populate this total to each continuous series. The desired output looks like the following:

obs	ID	A	B	C	SUMA
1	101	1	0	6	5
2	101	1	1	6	5
3	101	1	1	6	5
4	101	0	1	6	5
5	101	1	0	6	5
6	101	1	1	6	5
7	101	0	0	0	0
8	101	1	1	6	4
9	101	1	0	6	4
10	101	0	1	6	4
11	101	0	1	6	4
12	101	1	0	6	4
13	101	1	0	6	4
14	102	0	1	5	3
15	102	1	1	5	3
16	102	1	1	5	3
17	102	0	1	5	3
18	102	1	0	5	3

Re: Follow up Re: Accumulating totals in a series

PGStats — Thu, 13 Feb 2020 16:54:09 GMT

Sure.

data WANT; 

suma = 0;

do until(last.c);
	set have;  by id c notsorted;
	suma = suma + a;
	end;

do until(last.c);
	set have;  by id c notsorted;
	output;
	end;

run;

(untested)

Re: Follow up Re: Accumulating totals in a series

Tami99 — Thu, 13 Feb 2020 18:09:57 GMT

Thank you so much for the quick reply. I tested the codes and got the following results.

obs	ID	A	B	C	SUMA
1	101	1	0	6	.
2	101	1	1	6	.
3	101	1	1	6	.
4	101	0	1	6	.
5	101	1	0	6	.
6	101	1	1	6	5
7	101	0	0	0	0
8	101	1	1	6	.
9	101	1	0	6	.
10	101	0	1	6	.
11	101	0	1	6	.
12	101	1	0	6	.
13	101	1	0	6	4
14	102	0	1	5	.
15	102	1	1	5	.
16	102	1	1	5	.
17	102	0	1	5	.
18	102	1	0	5	3

Is there a way to fill those missing values with the accumulated total as below? Thank you!!!

obs	ID	A	B	C	SUMA
1	101	1	0	6	5
2	101	1	1	6	5
3	101	1	1	6	5
4	101	0	1	6	5
5	101	1	0	6	5
6	101	1	1	6	5
7	101	0	0	0	0
8	101	1	1	6	4
9	101	1	0	6	4
10	101	0	1	6	4
11	101	0	1	6	4
12	101	1	0	6	4
13	101	1	0	6	4
14	102	0	1	5	3
15	102	1	1	5	3
16	102	1	1	5	3
17	102	0	1	5	3
18	102	1	0	5	3

Re: Follow up Re: Accumulating totals in a series

PGStats — Thu, 13 Feb 2020 18:46:18 GMT

Looks like your have dataset already contained variable suma. Try this then:

data WANT; 

suma = 0;

do until(last.c);
	set have(drop=suma);  by id c notsorted;
	suma = suma + a;
	end;

do until(last.c);
	set have(drop=suma);  by id c notsorted;
	output;
	end;

run;

Re: Follow up Re: Accumulating totals in a series

Tami99 — Thu, 13 Feb 2020 21:07:54 GMT

Yep!!! Now it works wonders!!! Thank YOU!!!

ID	A	B	C
101	1	0	6
101	1	1	6
101	1	1	6
101	0	1	6
101	1	0	6
101	1	1	6
101	0	0	0
101	1	1	6
101	1	0	6
101	0	1	6
101	0	1	6
101	1	0	6
101	1	0	6
102	0	1	5
102	1	1	5
102	1	1	5
102	0	1	5
102	1	0	5

obs	ID	A	B	C
1	101	1	0	6
2	101	1	1	6
3	101	1	1	6
4	101	0	1	6
5	101	1	0	6
6	101	1	1	6
7	101	0	0	0
8	101	1	1	6
9	101	1	0	6
10	101	0	1	6
11	101	0	1	6
12	101	1	0	6
13	101	1	0	6
14	102	0	1	5
15	102	1	1	5
16	102	1	1	5
17	102	0	1	5
18	102	1	0	5

ID	A	B	C
101	1	0	6
101	1	1	6
101	1	1	6
101	0	1	6
101	1	0	6
101	1	1	6
101	0	0	0
101	1	1	6
101	1	0	6
101	0	1	6
101	0	1	6
101	1	0	6
101	1	0	6
102	0	1	5
102	1	1	5
102	1	1	5
102	0	1	5
102	1	0	5

obs	ID	A	B	C
1	101	1	0	6
2	101	1	1	6
3	101	1	1	6
4	101	0	1	6
5	101	1	0	6
6	101	1	1	6
7	101	0	0	0
8	101	1	1	6
9	101	1	0	6
10	101	0	1	6
11	101	0	1	6
12	101	1	0	6
13	101	1	0	6
14	102	0	1	5
15	102	1	1	5
16	102	1	1	5
17	102	0	1	5
18	102	1	0	5

ID	A	B	C
101	1	0	6
101	1	1	6
101	1	1	6
101	0	1	6
101	1	0	6
101	1	1	6
101	0	0	0
101	1	1	6
101	1	0	6
101	0	1	6
101	0	1	6
101	1	0	6
101	1	0	6
102	0	1	5
102	1	1	5
102	1	1	5
102	0	1	5
102	1	0	5

obs	ID	A	B	C
1	101	1	0	6
2	101	1	1	6
3	101	1	1	6
4	101	0	1	6
5	101	1	0	6
6	101	1	1	6
7	101	0	0	0
8	101	1	1	6
9	101	1	0	6
10	101	0	1	6
11	101	0	1	6
12	101	1	0	6
13	101	1	0	6
14	102	0	1	5
15	102	1	1	5
16	102	1	1	5
17	102	0	1	5
18	102	1	0	5