Hi all,
I know I did this before, but can't seem to get this to work right now. I have a dataset that looks something like this:
Date State County CensusTract Value StdErr
01Jan2011 01 1001 1001020200 50.40 4.23
01Jan2011 01 1001 1001020300 29.47 1.39
01Jan2011 01 1001 1001020400 68.51 2.77
01Jan2011 01 1003 1003010200 5.38 8.47
01Jan2011 01 1003 1003010300 18.78 6.24
So I have daily values for each census tract within each county within each state. What I want is something that summarizes the maximum value (as well as the mean and the median) for each county in a day. So I want to end up with something like this, which results in a dataset that just has one observation per county per day:
Date State County Max_DailyValue Mean_DailyValue Med_DailyValue
01Jan2011 01 1001 100.84 80.11 64.58
01Jan2011 01 1003 68.53 47.22 33.46
01Jan2011 01 1005 85.66 55.81 45.28
01Jan2011 01 1006 74.82 53.27 47.63
I also want to calculate the population-weighted average value for the county, but haven't figured that out yet.
I've been trying with variations on this code, but it's not quite right. Any advice is appreciated. Thanks!
proc sql; create table county_2011 as select year, date, statefips, countyfips, max(value) as max_value, median(value) as med_value, mean(value) as mean_value from tractfinal_2011 order by date, countyfips; quit;
... View more