BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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;


1 ACCEPTED SOLUTION

Accepted Solutions

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

7 REPLIES 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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