CASE WHEN issue in PROC SQL

greg6363 — Fri, 28 Feb 2020 21:09:14 GMT

I am compiling dollar totals and unit totals from a data set using PROC SQL. The dollar totals are fine but the output totals for the units are all the same. I've enclosed the code and the output file below. As you can see, I have set conditions with the CASE WHEN statement for each stage and used count (for units) and sum (for dollars). I don't know why I get the same total for each group. I need a second set of eyes to look at what is wrong. Any feedback is appreciated. Thanks.

proc sql;

create table current_balances as

select datepart(date) as date format mmddyy8., count(loan_id) as accts format comma8.,

count(case when days_past_due = 0 then loan_id

else 0 end) as count_current_accts format=comma8.,

sum(case when days_past_due = 0 then amount_due

else 0 end) as amt_current format=dollar16.2,

count(case when days_past_due ge 1 and days_past_due le 5

then loan_id else 0 end) as count_1_5_DPD format=comma8.,

sum(case when days_past_due ge 1 and days_past_due le 5

then amount_due else 0 end) as amt_1_5_DPD format=dollar16.2,

count(case when days_past_due ge 6 and days_past_due le 10

then loan_id else 0 end) as count_6_10_DPD format=comma8.,

sum(case when days_past_due ge 6 and days_past_due le 10

then amount_due else 0 end) as amt_6_10_DPD format=dollar16.2,

date accts count_current_accts amt_current count_1_5_DPD amt_1_5_DPD

2/27/20 95,538 95,538 $46,964.47 95,538 $97,741.06

Re: CASE WHEN issue in PROC SQL

PaigeMiller — Fri, 28 Feb 2020 21:14:25 GMT

The COUNT() function adds 1 if a number is present, and does not add one if the variable is missing. So whether the value of the variable is set to 0 or set to loan_id, these all add 1. Try setting the value to missing instead of setting it to zero.

Re: CASE WHEN issue in PROC SQL

greg6363 — Fri, 28 Feb 2020 21:25:07 GMT

It worked. Thanks so much.

topic CASE WHEN issue in PROC SQL in SAS Programming

CASE WHEN issue in PROC SQL

Re: CASE WHEN issue in PROC SQL

Re: CASE WHEN issue in PROC SQL