Sum a coalesce

[#1]

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;

[#2]

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 %?

[RW9]

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;

[#4]

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?

[RW9]

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;

[#6 · Accepted solution]

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;

[RW9]

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.

[#8]

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