DATA Step, Macro, Functions and more

Proc SQL - Display all cateorgies

Reply
Contributor
Posts: 55

Proc SQL - Display all cateorgies

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 Bronx2
Bronx Park and Fordham3
High Bridge and Morrisania4
Hunts Point and Mott Haven2
Central Harlem10
Chelsea and Clinton1
East Harlem

42

 

Super User
Posts: 19,799

Re: Proc SQL - Display all cateorgies

Not within Proc SQL, easily. 

 

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

Regular Contributor
Posts: 233

Re: Proc SQL - Display all cateorgies

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;
Super User
Posts: 11,343

Re: Proc SQL - Display all cateorgies

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.

Super User
Posts: 10,028

Re: Proc SQL - Display all cateorgies

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;
 

 

x.png

Ask a Question
Discussion stats
  • 4 replies
  • 222 views
  • 0 likes
  • 5 in conversation