Desktop productivity for business analysts and programmers

Sum statement

Reply
Contributor
Posts: 26

Sum statement

Hi everyone,

 

I'm trying to sum based on a condition. Data below:

 

cust_IDis_loanbalance
5001$7,465
5000$658
5001$2,487
5000$7,824
6000$232,564
6000$224
6001$21
6001$784

 

 

I want to do like a

 

data example;

set example_data;

if is_loan = 1 then sum(balance) output = total_loans;

if is_loan = 0 then sum(balance) output = total_account;

run;

 

So the output looks like this below:

 

cust_IDis_loanbalancetotal_loanstotal_account
5001$7,465 $  9,952.00 $      8,482.00
5000$658 $  9,952.00 $      8,482.00
5001$2,487 $  9,952.00 $      8,482.00
5000$7,824 $  9,952.00 $      8,482.00
6000$232,564 $     805.00 $  232,788.00
6000$224 $     805.00 $  232,788.00
6001$21 $     805.00 $  232,788.00
6001$784 $     805.00 $  232,788.00

 

So it is suming based on a condition and summing by customer_ID.

 

Any help is appreciated.

Thanks

Super User
Super User
Posts: 9,193

Re: Sum statement

Its not really clear what you want here, also post test data in the form of a datastep so we have something to work with.  At a guess:

proc sql;
  create table WANT as 
  select  A.CUST_ID,
          A.IS_LOAN,
          A.BALANCE,
          B.TOTAL_LOANS,
          C.TOTAL_ACCOUNT
  from    HAVE A
  left join (select CUST_ID,sum(case when IS_LOAN=1 then BALANCE else . end) as TOTAL_LOANS) B
  on       A.CUST_ID=B.CUST_ID
  left join (select CUST_ID,sum(case when IS_LOAN=0 then BALANCE else . end) as TOTAL_ACCOUNT) C
  on       A.CUST_ID=C.CUST_ID;
quit;
Super User
Posts: 6,534

Re: Sum statement

While I think the RW9 solution would work, my SQL isn't strong enough to be sure.  So here's a DATA step approach:

 

data want;

total_loans=0;

total_account=0;

do until (last.cust_ID);

   set have;

   by cust_ID;

   if is_loan=0 then total_account + balance;

   else total_loans + balance;

run;

do until (last.cust_ID);

   set have;

   by cust_ID;

   output;

end;

run;

 

It's optional whether you should set the total variables to 0 or missing at the start ... use whichever you would like to appear in your data set if there aren't any such observations.

Super Contributor
Posts: 338

Re: Sum statement

Posted in reply to Astounding

Just a bit shorter SQL that might do what you want Smiley Happy

 

proc sql;

select cust_id

,is_loan, balance

,sum(case is_loan when 1 then balance end) as total_loans

,sum(case is_loan when 0 then balance end) as total_account

from example_data

group by cust_id

;

quit;

Super User
Super User
Posts: 7,845

Re: Sum statement

[ Edited ]

You can definitely conditionally execute a SUM statement. A SUM statement takes the form

variable + expression;

Here is code to sum the loans, non_loans and everything into separate running totals.

data want ;
  set have ;
  if is_loan then total_loans+balance;
  else total_non_loans+balance;
  total_amount+balance;
  format total: dollar13. ;
run;

 

image.png

But if you want to have the total BY id then you do not really need (or want) a SUM statement since then you would need to reset the sum when you started and new group.  A double DOW works well for calculating over a group and carrying the vlaue onto all rows.

data want ;
  do until(last.cust_id);
    set have ;
    by cust_id;
    if is_loan then total_loans=sum(total_loans,balance,0);
    else total_non_loans=sum(total_non_loans,balance,0);
  end;
  total_amount=sum(total_loans,total_non_loans);
  do until(last.cust_id);
    set have ;
    by cust_id;
    output;
  end;
  format total: dollar13. ;
run;

image.png

Ask a Question
Discussion stats
  • 4 replies
  • 155 views
  • 0 likes
  • 5 in conversation