09-05-2017 03:07 AM
I'm trying to sum based on a condition. Data below:
I want to do like a
if is_loan = 1 then sum(balance) output = total_loans;
if is_loan = 0 then sum(balance) output = total_account;
So the output looks like this below:
|500||1||$7,465||$ 9,952.00||$ 8,482.00|
|500||0||$658||$ 9,952.00||$ 8,482.00|
|500||1||$2,487||$ 9,952.00||$ 8,482.00|
|500||0||$7,824||$ 9,952.00||$ 8,482.00|
|600||0||$232,564||$ 805.00||$ 232,788.00|
|600||0||$224||$ 805.00||$ 232,788.00|
|600||1||$21||$ 805.00||$ 232,788.00|
|600||1||$784||$ 805.00||$ 232,788.00|
So it is suming based on a condition and summing by customer_ID.
Any help is appreciated.
09-05-2017 04:18 AM
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;
09-05-2017 06:20 AM
While I think the RW9 solution would work, my SQL isn't strong enough to be sure. So here's a DATA step approach:
do until (last.cust_ID);
if is_loan=0 then total_account + balance;
else total_loans + balance;
do until (last.cust_ID);
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.
09-05-2017 09:34 AM
Just a bit shorter SQL that might do what you want
,sum(case is_loan when 1 then balance end) as total_loans
,sum(case is_loan when 0 then balance end) as total_account
group by cust_id
09-05-2017 10:44 AM - edited 09-05-2017 10:52 AM
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;
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;