topic Sum column values for a variable by another variable in SAS Programming

Sum column values for a variable by another variable

jeremy4 — Tue, 23 Jul 2019 15:40:24 GMT

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;

Re: Sum column values for a variable by another variable

Shmuel — Tue, 23 Jul 2019 15:48:47 GMT

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;

Re: Sum column values for a variable by another variable

Reeza — Tue, 23 Jul 2019 15:46:45 GMT

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;

Re: Sum column values for a variable by another variable

Patrick — Tue, 23 Jul 2019 15:50:54 GMT

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;