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?
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