topic Re: PROC SQL not counting non existent entries in SAS Programming

PROC SQL not counting non existent entries

K_S — Mon, 09 Nov 2020 22:27:44 GMT

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;

Re: PROC SQL not counting non existent entries

Reeza — Mon, 09 Nov 2020 22:35:38 GMT

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;

Re: PROC SQL not counting non existent entriesI

K_S — Mon, 09 Nov 2020 22:42:02 GMT

I think I really overcomplicated this!

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

Re: PROC SQL not counting non existent entries

PGStats — Tue, 10 Nov 2020 02:37:36 GMT

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)