Proc SQL left join with aggregate function omitting zeroes

Question

I have two data sets in SAS 9.4. One is type_list, which contains 14 different account types. The other is account_list, which contains thousands of accounts.

I'm trying to get a list of all types with a count of how many accounts in each type meet certain criteria. Crucially, if there are zero matching accounts, I'd like a row with zero in the output. For example:

TYPE    ACCOUNTS
type-1  104
type-2  0
type-3  56
...     ...

Here's the query I'm using:

PROC SQL;
CREATE TABLE summary AS
SELECT
   type_list.type,
   COUNT(account_list.account_number) AS accounts
FROM type_list
LEFT JOIN account_list
ON type_list.type = account_list.type
WHERE account_list.curr_balance > 1000
GROUP BY type_list.type;
QUIT;

The actual output I'm getting doesn't have a row for types that don't have any matching accounts. For example:

TYPE    ACCOUNTS
type-1  104
type-3  56
...     ...

Does this answer your question? [Left Join With Where Clause](https://stackoverflow.com/questions/4752455/left-join-with-where-clause) — philipxy, Aug 18 '22 at 13:51
LEFT JOIN returns INNER JOIN rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After a LEFT JOIN a WHERE, INNER JOIN or HAVING that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS This is a faq. But one must pin down via a [mre] & write many clear, concise & precise phrasings of one's question/problem/goal to search reasonably. — philipxy, Aug 18 '22 at 14:15

Richard · Answer 1 · 2022-08-18T15:42:12.197

Left join the types with a computational sub-query of the accounts. Use COALESCE to handle the case of some type never occurring in accounts.

Example:

Type 7 never occurs in accounts.

data types;
  do type = 1 to 14;
    input count @@;
    output;
  end;
  stop;
  datalines;
104 0 56 123 4 0 7 8 9 0 11 12 13 14
;

data accounts;
  call streaminit (20220818);
  do id = 1 to 1e5;
    do until (type ne 7);
      type = rand('integer', 1, 14);
    end;
    balance = rand ('integer', 25, 25000);
    output;
  end;
run;

proc sql;
  create table want as
  select 
    types.type
  , types.count as type_count
  , coalesce(amc.accounts_count,0) as accounts_count
  from 
    types
  left join
    (select type, count(*) as accounts_count
     from accounts
     where balance > 21000
     group type
    ) as amc /* counts of accounts meeting criteria */
  on
    types.type = amc.type
  ;

Proc SQL left join with aggregate function omitting zeroes

1 Answers1