Now that we can see the input data and the output desired, we can write code to get from one to the other (but I'm still not sure you have provided the desired output).
Data Ped_fatal;
Length State $54 County $54;
Input State$ County$ Year Ped_rates ;
Datalines;
ALABAMA Autauga 2002 1.51
ALABAMA Autauga 2003 .
ALABAMA Autauga 2005 8.28
ALABAMA Autauga 2007 .
ALABAMA Autauga 2008 14.27
ALABAMA Autauga 2009 54.05
ALABAMA Autauga 2010 .
ALABAMA Autauga 2011 .
ALABAMA Autauga 2012 19.41
ALABAMA Autauga 2013 6.65
ALABAMA Autauga 2014 29.13
ALABAMA Autauga 2015 1.84
ALABAMA Autauga 2016 .
ALABAMA Autauga 2017 107.93
ALABAMA Baldwin 2001 4.46
ALABAMA Baldwin 2002 41.69
ALABAMA Baldwin 2003 22.15
ALABAMA Baldwin 2004 102.13
ALABAMA Baldwin 2005 0.33
ALABAMA Baldwin 2006 0.16
ALABAMA Baldwin 2007 9.59
ALABAMA Baldwin 2008 16.75
;
Run;
data all_years;
do year=2001 to 2017;
output;
end;
run;
proc sql;
create table all_counties as select distinct state,county from ped_fatal;
create table all_counties_years as select * from all_counties,all_years
order by state,county,year;
quit;
data ped_fatal2;
merge ped_fatal(in=in1) all_counties_years(in=in2);
by state county year;
if in2 and not in1 then ped_rates=.;
prev_rate=lag(ped_rates);
if not first.county then change = (ped_rates-prev_rate)/prev_rate;
drop prev_rate;
run;
Although, this part is still not clear to me
I do not want to calculate changes in rates across various years, unless there is too many missing variables. Then I will keep what I have, and calculate 2001 as the baseline year for changes in rates.
and it seems like the changes you are computing are still unrelated to the input data, and this continues to be a problem. Can't you take the Autauga and Baldwin raw data only, and provide the exact calculated change rate (do the calculations yourself) for Autauga and Baldwin only and show us those exact calculated change rates?
... View more