BookmarkSubscribeRSS Feed
Scott86
Obsidian | Level 7

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Astounding
PROC Star

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.

FredrikE
Rhodochrosite | Level 12

Just a bit shorter SQL that might do what you want 🙂

 

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;

Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 4 replies
  • 2011 views
  • 0 likes
  • 5 in conversation