Summing multiple variables by group

[jbscholten]

Hello, 

I'm working to create a function that will sum multiple variables by groups. I know how to sum one variable by group using a retain statement, but I'm having trouble looping through variables. 

The data is in this format: 

Group	value000	value001	value002	value003	value004	value005
1	1	2	3	4	5	6
1	7	8	9	10	11	12
1	13	14	15	16	17	18
1	19	20	21	22	23	24
1	25	26	27	28	29	30
1	31	32	33	34	35	36
1	37	38	39	40	41	42
2	43	44	45	46	47	48
2	49	50	51	52	53	54
2	55	56	57	58	59	60
2	61	62	63	64	65	66
2	67	68	69	70	71	72

I'd like the data to be in this format, that is 1 row for each group, and a sum of all values in said group. The actual dataset will have thousands of groups, and 41 variables per group (all in the format valuexxx). The number of observations per group will vary between 4-50.

Group	value000	value001	value002	value003	value004	value005
1	133	140	147	154	161	168
2	275	280	285	290	295	300

[Reeza · Accepted solution]

Try using a summary procedure, such as PROC MEANS instead. This is a trivial calculation to summarize data. 

proc means data=have nway;
class group;
var value000-value005;
output out=want  sum = ;
run;

A data step would be inefficient and tedious to program. 

---

@jbscholten wrote:

Hello, 

I'm working to create a function that will sum multiple variables by groups. I know how to sum one variable by group using a retain statement, but I'm having trouble looping through variables. 

The data is in this format: 

Group	value000	value001	value002	value003	value004	value005
1	1	2	3	4	5	6
1	7	8	9	10	11	12
1	13	14	15	16	17	18
1	19	20	21	22	23	24
1	25	26	27	28	29	30
1	31	32	33	34	35	36
1	37	38	39	40	41	42
2	43	44	45	46	47	48
2	49	50	51	52	53	54
2	55	56	57	58	59	60
2	61	62	63	64	65	66
2	67	68	69	70	71	72

I'd like the data to be in this format, that is 1 row for each group, and a sum of all values in said group. The actual dataset will have thousands of groups, and 41 variables per group (all in the format valuexxx). The number of observations per group will vary between 4-50.

Group	value000	value001	value002	value003	value004	value005
1	133	140	147	154	161	168
2	275	280	285	290	295	300

---

[PGStats]

Using a variable list, you can write a data step:

data want;
do until (last.group);
    set have; by group;
    array v{*} value:;
    array s{50};
    do i = 1 to dim(v);
        s{i} = sum(s{i}, v{i});
        end;
    end;
do i = 1 to dim(v);
    v{i} = s{i};
    end;
output;
keep Group value:;
run;