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;
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;
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 %?
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;
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?
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;
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;
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.
Not sure what happened as I selected your recent comment as corrected and messaged thanking you for your help.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.