Sum column values for a variable by another variable

[jeremy4]

Hi,

I have two variables that I want to focus on - set and balance. Is there code that would sum up balance by set i.e. in the 'Results' tab, there would be a summary table that would show that set=1 has a total balance of 107, set=2 has a total balance of 46 and set=3 has a total balance of 95. The below example is a small extract but there are over 100,000 records in my dataset, so would like a quick overview to see what the total balance would be by set (1, 2 or 3). Thanks!

Set   Balance

1          25

1          82

2          36

3          80

3          15

2          10

Current code:

proc summary data=test;

   var balance;

   by set;

run;

[Shmuel · Accepted solution]

Pay attention, in your code you used BY SET, which assumes that the data set is already sorted by SET.

If you have no huge amount of sets you can use CLASS instead of BY and be free of sorting the data set.

Anyhow you missed the OUTPUT statement:

proc summary data=test;

   var balance;

   by set;   /* or class set; */

  output out=want sum=;

run;

[Reeza]

Did your code not work?
Or do you need something else?

proc sort data=test; by set;run;
proc means data=test sum;
by set;
var balance;
run;

[Patrick]

Your code looks o.k. to me (if the source data is sorted by variable SET). Here just for fun alternative code using SQL.

proc sql;
/*  create table want as*/
  select 
    set, sum(balance) as sum_balance
  from test
  group by set
  ;
quit;