PROC SQL not counting non existent entries

[K_S]

Hello, 

I am required to report incidents of X by year, state, mode of transport.

I have a decade worth of data and I used proc sql to count the incidents by mode of transport.

The problem is that I get no 0 counts, so I have some missing rows for some year+state+mode combinations. 

How do I fix this issue? I want to have all years and all states and all modes showing with 0s as the count, but the 0 count rows just don't appear.

PROC SQL;
CREATE TABLE WANT AS SELECT
YEAR, STATE, MODE, count(MODE) AS VALUE FROM HAVE
GROUP BY YEAR, STATE, MODE; QUIT;

[Reeza]

So you want to count something that doesn't exist in your data?
That's not possible, within SQL especially.

However, PROC FREQ has the SPARSE option which likely provides what you need - which is all combinations.

proc freq data=have noprint;
table year*state*mode / out=want sparse;
run;

[K_S]

I think I really overcomplicated this!

No need to use proc sql here.  proc freq does a wonderful job...

[PGStats]

If you ever need to do this counting with SQL, here is a way to do it:

PROC SQL;
CREATE TABLE WANT AS 
SELECT
	y.YEAR, 	
	s.STATE, 	
	m.MODE, 
	count(h.MODE) AS VALUE 
FROM 
	(select unique year from HAVE) as y cross join
	(select unique state from HAVE) as s cross join
	(select unique mode from HAVE) as m natural left join
	HAVE as h
GROUP BY y.YEAR, s.STATE, m.MODE; 
QUIT;

(untested)