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 |
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;
Please show us the input data.
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;
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;
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;
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?
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.