Variable Counts, Proc Summary

[mary_mcneill]

Hello, I am looking to get monthly counts by a variable Region as well as the monthly counts for each county within that region. I have tried to use proc summary to do this; however, it will provide 0 values for all the counties  for every region and not just the counties within each Region. Is there a way to limit the county variable to only include the counties within each region as well as a total count for the region? 

Preferable output:

Region	County	1 - Jan	2 - Feb	3 - Mar
Region 1	County A	0	0	0
Region 1	County M	5	4	7
Region 1	County F	0	2	1
Region 1	County Z	12	20	17
Total Region 1		17	26	25
Region 2	County N	0	0	0
Region 2	County G	1	0	2
Region 2	County P	3	1	0
Total Region 2		4	1	2

[PaigeMiller]

Please show us the input data.

[mary_mcneill]

data mydata;

  infile datalines;

  input county month region ;

return;

datalines;

M 1 1

M 1 1

M 1 1

M 1 1

M 1 1

M 2 1

M 2 1

M 2 1

M 2 1

M 3 1

M 3 1

M 3 1

M 3 1

M 3 1

M 3 1

M 3 1

F 2 1

F 2 1

F 3 1 

G 1 2

G 3 2

G 3 2

P 1 2

P 1 2

P 1 2

P 2 2

;

run;

[mary_mcneill]

Sorry I forgot to designate my character variable. I also excluded County Z due to volume.

data mydata;

  infile datalines;

  input county $ month region ;

return;

datalines;

M 1 1

M 1 1

M 1 1

M 1 1

M 1 1

M 2 1

M 2 1

M 2 1

M 2 1

M 3 1

M 3 1

M 3 1

M 3 1

M 3 1

M 3 1

M 3 1

F 2 1

F 2 1

F 3 1 

G 1 2

G 3 2

G 3 2

P 1 2

P 1 2

P 1 2

P 2 2

;

run;

[PaigeMiller · Accepted solution]

When you want a specific formatting of a table, PROC REPORT seems like a better way to get there.

proc report data=mydata;
	columns region county month;
	define region/group "Region";
	define county/group "County";
	define month/across "Month";
run;

[mary_mcneill]

Hi Paige,

Thanks that works! Do you know how I would get a total region count (total Region1, total region2) as well as any county within the region to display that did not have any values (i.e. Region1 CountyA, Region2 CountyN). Perhaps this would have to be done in a format?

[PaigeMiller]

proc report data=mydata;
	columns region county month;
	define region/group "Region";
	define county/group "County";
	define month/across "Month" format=mon. order=internal;
	break after region/summarize;
run;

Naturally, this can be formatted a little nicer, but that's what I got right now.

[JeanDo]

Hello,

I think exactly the same way, Paige, and will propose the same solution with the sum by region:

proc report data=mydata ;
column region county month;
define region / group;
define county /group;
define month / across;
break after region /summarize;
run;