CASE WHEN issue in PROC SQL

[greg6363]

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

[PaigeMiller · Accepted solution]

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.

[greg6363]

It worked. Thanks so much.