topic Re: Sum a coalesce in SAS Programming

Sum a coalesce

anonymous_user — Wed, 17 Sep 2014 09:02:42 GMT

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;

Re: Sum a coalesce

anonymous_user — Wed, 17 Sep 2014 09:22:52 GMT

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

Re: Sum a coalesce

RW9 — Wed, 17 Sep 2014 09:33:15 GMT

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;

Re: Sum a coalesce

anonymous_user — Wed, 17 Sep 2014 09:45:19 GMT

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?

Re: Sum a coalesce

RW9 — Wed, 17 Sep 2014 09:52:47 GMT

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;

Re: Sum a coalesce

anonymous_user — Wed, 17 Sep 2014 10:29:16 GMT

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

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;

Re: Sum a coalesce

RW9 — Wed, 17 Sep 2014 10:48:19 GMT

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.

Re: Sum a coalesce

anonymous_user — Wed, 17 Sep 2014 10:56:18 GMT

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