Sum a coalesce

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 138
Accepted Solution

Sum a coalesce

Hi I have a coalesce statement within my code but need to -

1)sum the balance

2)divide the balance for the month then divide it to get a %.

I have tried a sum case when statement but im not having any luck.

My code -

proc sql;

create table work.dcm as

select

month_date,

coalesce(dcdefbal,dccjsbal,dcdlgbal) as arrs_elsewhere

from

dcm.dcmaug14;

quit;



Accepted Solutions
Solution
‎09-17-2014 06:29 AM
Frequent Contributor
Posts: 138

Re: Sum a coalesce

This is my code but I get an error message -

ERROR: Column dcdlgbal could not be found in the table/view identified with the correlation name

       A.

ERROR: Column balance_outstanding could not be found in the table/view identified with the

       correlation name B.

ERROR: The COALESCE function requires its arguments to be of the same data type.

proc sql;
create table work.dcm_arrears_elsewhere as
select
a.month_date,
sum(coalesce(a.dcdefbal,a.dccjsbal,a.dcdlgbal)) as arrears_elsewhere,
(calculated arrears_elsewhere / b.balance_outstanding) * 100

from
work.dcm_dataset as a
left join work.basel_dataset as b
on a.accnum = b.accnum;

quit;

View solution in original post


All Replies
Frequent Contributor
Posts: 138

Re: Sum a coalesce

I have worked out the sum

sum(coalesce(dcdefbal,dccjsbal,dcdlgbal)) as arrs_elsewhere

I just need to know how I didvid it to get the %?

Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: Sum a coalesce

Hi,

Use the CALCULATED command:

proc sql;

     create table work.dcm as

     select     month_date,

                    sum(coalesce(dcdefbal,dccjsbal,dcdlgbal)) as arrs_elsewhere ,

                    (CALCULATED ARRS_ELSEWHERE / BALANCE) * 100

     from        dcm.dcmaug14;

quit;

Frequent Contributor
Posts: 138

Re: Sum a coalesce

Thanks for the response RW9. The problem I have is that there isn't a balance within this table so the SQL above wont work.

I do have balance_outstanding in another table, gbasel.baselaug14, would I need to try joining that together to do this?

Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: Sum a coalesce

Yep,

proc sql;

     create table work.dcm as

     select     A.month_date,

                    sum(coalesce(A.dcdefbal,A.dccjsbal,A.dcdlgbal)) as arrs_elsewhere ,

                    (CALCULATED ARRS_ELSEWHERE / B.BALANCE) * 100

     from        dcm.dcmaug14 A

     left join   gbasel.baselaug14 B

     on           A.....=B....  /* Put the variables to merge on here, e.g. month_date?

     ...         

;

quit;

Solution
‎09-17-2014 06:29 AM
Frequent Contributor
Posts: 138

Re: Sum a coalesce

This is my code but I get an error message -

ERROR: Column dcdlgbal could not be found in the table/view identified with the correlation name

       A.

ERROR: Column balance_outstanding could not be found in the table/view identified with the

       correlation name B.

ERROR: The COALESCE function requires its arguments to be of the same data type.

proc sql;
create table work.dcm_arrears_elsewhere as
select
a.month_date,
sum(coalesce(a.dcdefbal,a.dccjsbal,a.dcdlgbal)) as arrears_elsewhere,
(calculated arrears_elsewhere / b.balance_outstanding) * 100

from
work.dcm_dataset as a
left join work.basel_dataset as b
on a.accnum = b.accnum;

quit;

Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: Sum a coalesce

Not sure why you have marked your response as correct, have you answered yourself?

Looking at the errors - you need to check what the variables are called in the relevant datasets:

work.dcm_dataset -> This dataset should contain the variables month_date, dcdefbal, dccjsbal, and dcdlgbal.

work.basel_dataset -> This should at minimum have accnum, and balance_outstanding.

Frequent Contributor
Posts: 138

Re: Sum a coalesce

Not sure what happened as I selected your recent comment as corrected and messaged thanking you for your help.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 600 views
  • 0 likes
  • 2 in conversation