DATA Step, Macro, Functions and more

Proc Summary By

Regular Contributor
Posts: 192

Proc Summary By

How do you get  totals for each county by type:

Name County Total   Type

site1     A          34         2

site2     B          35         1                

site3     A          100       3         

site4     C          12         3

site5     B          23         3

site6     A          25         2

site7     C          56         1

site8     A          88          2

site9     B          90          1

                 Type1     Type2     Type3

countyA    0             147          100

countyB    125          0               23

countyC    56            0               12

I tried this but it doesn't work. Any help you can give will be much appreciated!

PROC SUMMARY DATA=reported order=data;

     CLASS county type;

     VAR total;

     BY type;

OUTPUT OUT= reportedtype SUM=total;


Posts: 8,743

Re: Proc Summary By


PROC SUMMARY/MEANS will give you numbers, but not the structure you want (with county going down the rows and type going across the columns). My tendency, to create this type of report is to use PROC TABULATE or PROC REPORT. Given the variables you listed, I would suggest code similar to the code shown below.


options missing=0;

ods listing close;


ods html file='c:\temp\tab_report.html';

proc tabulate data=reported f=comma9.;

title 'TABULATE';

  class county type;

  var total;

  table county all,

        total*type*sum=' ';


proc report data=reported nowd;

title 'REPORT';

  column county total,type;

  define county/ group;

  define type / across;

  define total / sum;

  rbreak after / summarize;


ods html close;

Regular Contributor
Posts: 192

Re: Proc Summary By

Thank you so very much!

Super User
Super User
Posts: 6,502

Re: Proc Summary By

You could just use PROC FREQ with WEIGHT statement and then TRANSPOSE the resulting "counts".

proc freq data=have ;

  tables county*type /noprint out=vertical ;

  weight total / zeros;


proc transpose data=vertical prefix=Type out=want (drop=_name_ _label_);

  by county ;

  id type ;

  var count;


proc print data=want;


Obs    County    Type1    Type2    Type3

1       A          0      147      100

2       B        125        0       23

3       C         56        0       12

Ask a Question
Discussion stats
  • 3 replies
  • 3 in conversation