SAS: include values that don't meet a threshold as missing

Question

I am using proc sql and proc report to create a table for my results:

proc sql;
create table newtable as
select location, year, month, count(distinct day) as daycount
from have
where threshold>90
group by location, year, month;
quit;

proc report data=newtable;
columns location month year, daycount;
define location/group;
define month/group;
define year/across;
define daycount/analysis sum;
run; 

The problem I have is that I want all locations, years, and months to appear in the report, even if cells have no values of threshold>90 (and would therefore present as missing). For example, there are 2 locations that have no values of threshold>90 for any month or year, and are excluded entirely from the report. How do I alter my code so that missings are included?

Answer 1

Your where statement filter out data where treshold is lower than 90. If you want to count distinct days only in treshold > 90 but not in the others use case instead:

proc sql;
    create table newtable as
        select location, year, month, case 
            when treshold > 90 then count(distinct day) 
            else 0 
        end as daycount
            from have
            group by location, year, month;
quit;