COUNT to return NULL as 0 in same table in SAS PROC SQL with GROUP BY

I am having a task where I have 1 table. There is a unique ID for transactions, transaction dates, and store names. My task would be to count the number of transactions every store made during March.

This, in itself, is simple:

proc sql;
CREATE TABLE marchtransactions AS SELECT DISTINCT COUNT(tr_id) , store_name
FROM transactions
WHERE MONTH(tr_date)=3
GROUP BY store_name;
quit;

This would lead to a table where a store is present only if it had a transaction in March. If a store does not have a transaction, it would be a NULL, and therefore, it will not be presented in the table at all.

While technically the task is fine as it is, I would really like to present every store in my table, even those who made no transaction in March. So I want to see the 0 count values as well.

I have seen many different scenarios on the internet: IFNULL function, LEFT JOIN, RIGHT JOIN, etc., but this is only one table, and in SAS PROC SQL, there is no such thing as IFNULL.

I have tried something like making a helper table with the store names and use LEFT JOIN as well, but I had no success. I even tried using CASE WHEN, with no success at all (keep in mind, I am quite a beginner, I may have done one of these things wrong though) 🙁

Any idea how to make this work?

Answer

In SAS a boolean expression is evaluated as 1 (TRUE) or 0 (FALSE). So to “count” how many times a condition happens just SUM() the condition.

proc sql;
  CREATE TABLE marchtransactions AS
    SELECT store_name, sum(MONTH(tr_date)=3) as march_count
    FROM transactions
    GROUP BY store_name
  ;
quit;

If you have to do the same thing in some other SQL implementation just use a CASE clause.

sum(case when (MONTH(tr_date)=3) then 1 else 0 end) as march_count