topic Re: Proc SQL - Display all cateorgies in SAS Programming

Proc SQL - Display all cateorgies

hwangnyc — Mon, 18 Jul 2016 14:19:06 GMT

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

Re: Proc SQL - Display all cateorgies

Reeza — Mon, 18 Jul 2016 14:45:48 GMT

Not within Proc SQL, easily.

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

Re: Proc SQL - Display all cateorgies

gamotte — Mon, 18 Jul 2016 15:21:55 GMT

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;

Re: Proc SQL - Display all cateorgies

ballardw — Mon, 18 Jul 2016 15:58:11 GMT

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.

Re: Proc SQL - Display all cateorgies

Ksharp — Tue, 19 Jul 2016 05:49:38 GMT

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;