topic Re: Variable Counts, Proc Summary in SAS Programming

Variable Counts, Proc Summary

mary_mcneill — Tue, 14 Jan 2020 15:56:03 GMT

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

Re: Variable Counts, Proc Summary

PaigeMiller — Tue, 14 Jan 2020 15:58:32 GMT

Please show us the input data.

Re: Variable Counts, Proc Summary

mary_mcneill — Tue, 14 Jan 2020 16:10:50 GMT

data mydata;

infile datalines;

input county month region ;

return;

datalines;

M 1 1

M 2 1

M 3 1

F 2 1

F 3 1

G 1 2

G 3 2

P 1 2

P 2 2

;

run;

Re: Variable Counts, Proc Summary

mary_mcneill — Tue, 14 Jan 2020 16:15:20 GMT

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 2 1

M 3 1

F 2 1

F 3 1

G 1 2

G 3 2

P 1 2

P 2 2

;

run;

Re: Variable Counts, Proc Summary

PaigeMiller — Tue, 14 Jan 2020 16:29:55 GMT

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;

Re: Variable Counts, Proc Summary

mary_mcneill — Tue, 14 Jan 2020 16:38:45 GMT

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?

Re: Variable Counts, Proc Summary

JeanDo — Tue, 14 Jan 2020 16:40:15 GMT

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;

Re: Variable Counts, Proc Summary

PaigeMiller — Tue, 14 Jan 2020 16:40:44 GMT

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.