Proc SQL - Display all cateorgies

[hwangnyc]

Hi All,

My code:

PROC SQL;
 SELECT Neighborhoods, COUNT(childid) AS NewEnroll_ByCou_YTD
  FROM counts
  where '01jan2016'd <= dateeval <= &Marco_tdate
  group by Neighborhoods;
QUIT;

Gives me the output below. However, there are 35 other cateorgies that are associated with this variable. Is there a way to have them all listed? So in total it would be 42 rows, All the neighborhoods listed and zeros?

Neighborhoods NewEnroll_ByCou_YTD

Central Bronx	2
Bronx Park and Fordham	3
High Bridge and Morrisania	4
Hunts Point and Mott Haven	2
Central Harlem	10
Chelsea and Clinton	1
East Harlem	42

[Reeza]

Not within Proc SQL, easily. 

Look up PRELOADFMT and proc tabulate. You can also try proc freq with sparse option. 

[gamotte]

Hello,

With proc sql, maybe sometghing like this :

PROC SQL;
 SELECT Neighborhoods, COUNT(childid) AS NewEnroll_ByCou_YTD
  FROM counts
  where '01jan2016'd <= dateeval <= &Marco_tdate
  group by Neighborhoods

UNION

SELECT DISTINCT Neighborhoods, 0 AS  NewEnroll_ByCou_YTD
  FROM counts
WHERE dateeval<'01jan2016'd OR dateeval>&Marco_tdate;
QUIT;

[ballardw]

Not an elegant solution but create a base data set with the values for the variable and a count of 0.

Create a dataset from your counting code.

The create a report data set by updating the value in the base set from the counting set.

But for a report I would use @Reeza's Proc tabulate approach, especially if I'm doing this very often.

[Ksharp]

data levels;
sex='F';output;
sex='M';output;
sex='X';output;
run;

data class;
 set sashelp.class(in=ina) levels;
 w=ina;
run;

proc freq data=class;
table sex;
weight w/zeros;
run;