topic PROC SQL GROUP BY STATE AND COUNTY FIPS in SAS Procedures

PROC SQL GROUP BY STATE AND COUNTY FIPS

raheleh22 — Fri, 16 Feb 2024 01:54:26 GMT

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.

Re: PROC SQL GROUP BY STATE AND COUNTY FIPS

Patrick — Fri, 16 Feb 2024 03:04:02 GMT

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.