PROC SQL GROUP BY STATE AND COUNTY FIPS

[raheleh22]

I am working on a nationwide dataset where has my outcome variable at both county and state level. ealier I used PROC SQL  and group by statement to get a total deaths by state and here is my code: 

proc sql;
create table test1 as select *, count(*) as total_deaths from aggregatedallv1 group by State_Occurrence_FIPS  quit;

this gave me a columns as total per State. Now I wonder how to get total per county group by State. 

any advice is much appreciated. 

[Patrick · Accepted solution]

Assuming that in your source table HAVE you've got one row per death and you've got a variable for state and one for county then code along the line of below should work.

proc sql;
  create table test1 as 
    select 
      state
      ,county 
      ,count(*) as total_deaths 
    from have 
    group by state, county
  ;
quit;

If you want to get aggregated values (count, sum, ...) on different level (like state and state,county) using a single Proc then look into Proc Means.