topic Proc SQL display all categories in SAS Programming

Proc SQL display all categories

hwangnyc — Mon, 18 Jul 2016 14:10:32 GMT

Hi All,

My code:

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

Gives me this output:

SAS Output

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

The Neighborhoods variable has 35 more categories and I would like them all listed, even if they are all zeros. Is there a way to do this?

Thanks!

Re: Proc SQL display all categories

ChrisNZ — Wed, 27 Jul 2016 03:32:35 GMT

The 35 neighborhoods are not in the SAS table or they would have appeared.

Unless you mean that are there but with other dates?

In which case something like this may do:

PROC SQL;
   SELECT programtypeid, sum(   '01jan2016'd <= dateeval <= &tdate )   AS NewEnroll_ByCou_YTD
   FROM counts
   group by programtypeid;
QUIT;

Re: Proc SQL display all categories

toconero — Wed, 27 Jul 2016 07:06:09 GMT

Maybe the categories that they don't satify the condition they aren't listed.

I'm going to write another possibility.

PROC MEANS DATA=COUNTS NMISS N;
ID programtypeid;
VAR childid;
OUTPUT OUT=SALIDA(N=NewEnroll_ByCou_YTD);
RUN;
PROC SORT DATA=SALIDA;
BY PROGRAMTYPEID;
RUN;

You should include your condition with WHERE;

Re: Proc SQL display all categories

error_prone — Wed, 27 Jul 2016 07:30:07 GMT

Do you have a format to transform programtypeid to Neighborhoods?

Untested, assuming that a format exists and is named $Neighborhoods:

proc summary data=counts(where=('01jan2016'd <= dateeval <= &tdate)) completetypes nway;
  class programtypeid / preloadfmt order=data;
  format programmtypeid $Neighborhoods.;
  output out=work.CountComplete(drop=_type_ rename=(_freq_=NewEnroll_ByCou_YTD));
run;

proc print data=work.CountComplete noobs;
run;