BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jbscholten
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

 

 

View solution in original post

2 REPLIES 2
Reeza
Super User

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
Opal | Level 21

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 20020 views
  • 5 likes
  • 3 in conversation