Hi! I am needing to calculate annual rate changes, over a span of 17 years (2001-2017), and across over 3000 counties in US states. As of now, my code calculates the change of rate, however continues to use the first year (2001) as the "original rate" to derive the change of rate from. I would like to change this so then the change of rate can recalculate based on the year prior. For instance, the change of rate from 2001-2002, then 2002-2003, then 2003-2004...and so on. As of now, my code outputs change of rates from 2001-2002, 2001-2003, 2001-2004...and so one. Generally, I would like to change the baseline rate to change per year, to the year prior. I am on SAS version 9.4 Here is an example of my data: 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; Here is my current code: data want;
set Ped_fatal;
by state county year;
lyr1 = lag1(Ped_rate);
lyr2 = lag2(Ped_rate);
lyr3 = lag3(Ped_rate);
lyr4 = lag4(Ped_rate);
lyr5 = lag5(Ped_rate);
lyr6 = lag6(Ped_rate);
lyr7 = lag7(Ped_rate);
lyr8 = lag8(Ped_rate);
lyr9 = lag9(Ped_rate);
lyr10 = lag10(Ped_rate);
lyr11 = lag11(Ped_rate);
lyr12 = lag12(Ped_rate);
lyr13 = lag13(Ped_rate);
lyr14 = lag14(Ped_rate);
lyr15 = lag15(Ped_rate);
lyr16 = lag16(Ped_rate);
if first.county then count=1;
else count+1;
if count ge 1 then growth1yr = (Ped_rate - lyr1)/lyr1;
if count ge 2 then growth2yr = (Ped_rate - lyr2)/lyr2;
if count ge 3 then growth3yr = (Ped_rate - lyr3)/lyr3;
if count ge 4 then growth4yr = (Ped_rate - lyr4)/lyr4;
if count ge 5 then growth5yr = (Ped_rate - lyr5)/lyr5;
if count ge 6 then growth6yr = (Ped_rate - lyr6)/lyr6;
if count ge 7 then growth7yr = (Ped_rate - lyr7)/lyr7;
if count ge 8 then growth8yr = (Ped_rate - lyr8)/lyr8;
if count ge 9 then growth9yr = (Ped_rate - lyr9)/lyr9;
if count ge 10 then growth10yr = (Ped_rate - lyr10)/lyr10;
if count ge 11 then growth11yr = (Ped_rate - lyr11)/lyr11;
if count ge 12 then growth12yr = (Ped_rate - lyr12)/lyr12;
if count ge 13 then growth13yr = (Ped_rate - lyr13)/lyr13;
if count ge 14 then growth14yr = (Ped_rate - lyr14)/lyr14;
if count ge 15 then growth15yr = (Ped_rate - lyr15)/lyr15;
if count ge 16 then growth16yr = (Ped_rate - lyr16)/lyr16;
run;
proc print data=want;
var year state county Ped_rate lyr1 lyr2 lyr3 lyr4 growth1yr growth2yr growth3yr growth4yr;
run; My current output from the above code is the following: Year State County Ped_rate lyr1 lyr2 lyr3 lyr4 growthyr1 growthyr2 growthyr3 growthyr4 2002 ALABAMA Autauga 1.51 . . . . . . . . 2003 ALABAMA Autauga . 1.51 . . . . . . . 2005 ALABAMA Autauga 8.28 . 1.51 . . . 4.47 . . 2007 ALABAMA Autauga . 8.28 . 1.51 . . . . . 2008 ALABAMA Autauga 14.27 . 8.28 . 1.51 . 0.72 . 8.43 2009 ALABAMA Autauga 54.05 14.27 . 8.28 . 2.79 . 5.53 . 2010 ALABAMA Autauga . 54.05 14.27 . 8.28 . . . . 2011 ALABAMA Autauga . . 54.05 14.27 . . . . . 2012 ALABAMA Autauga 19.41 . . 54.05 14.27 . . -0.64 0.36 2013 ALABAMA Autauga 6.65 19.41 . . 54.05 -0.66 . . -0.88 2014 ALABAMA Autauga 29.13 6.65 19.41 . . 3.38 0.5 . . 2015 ALABAMA Autauga 1.84 29.13 6.65 19.41 . -0.94 -0.72 -0.91 . 2016 ALABAMA Autauga . 1.84 29.13 6.65 19.41 . . . . 2017 ALABAMA Autauga 107.93 . 1.84 29.13 6.65 . 57.8 2.7 15.24 2001 ALABAMA Baldwin 4.46 107.93 . 1.84 29.13 -0.96 . . . 2002 ALABAMA Baldwin 41.69 4.46 107.93 . 1.84 8.36 -0.61 . . 2003 ALABAMA Baldwin 22.15 41.69 4.46 107.93 . -0.47 3.97 -0.79 . 2004 ALABAMA Baldwin 102.13 22.15 41.69 4.46 107.93 3.61 1.45 21.92 -0.05 2005 ALABAMA Baldwin 0.33 102.13 22.15 41.69 4.46 -1 -0.99 -0.99 -0.93 2006 ALABAMA Baldwin 0.16 0.33 102.13 22.15 41.69 -0.52 -1 -0.99 -1 2007 ALABAMA Baldwin 9.59 0.16 0.33 102.13 22.15 59.55 28.2 -0.91 -0.57 2008 ALABAMA Baldwin 16.75 9.59 0.16 0.33 102.13 0.75 104.76 50.01 -0.84 Additionally, I would like to transpose my data in order to have a single variable named "Annual Change of Rate" to be one column, changing per year increase (from 2001-2017). Here is my current transpose code: Proc Transpose Data=Want out=Long prefix=Annual_rate_change ;
By State County;
Bar growth1yr growth2yr growth3yr growth4yr growth5yr growth6yr growth7yr growth8yr
growth9yr growth10yr growth11yr growth12yr growth13yr growth14yr growth15yr growth16yr;
run;
Data Revised_Rate;
Set Long;
year=input(substr(_name_, 7), 7.);
drop _name_;
Run;
Proc Print Data= Revised_Rate (obs=10);
Run; Here is my current output of the transpose code: STATE COUNTY ANNUAL_RATE_CHANGE1 ANNUAL_RATE_CHANGE2 ANNUAL_RATE_CHANGE3 ANNUAL_RATE_CHANGE4 ANNUAL_RATE_CHANGE5 ANNUAL_RATE_CHANGE6 ANNUAL_RATE_CHANGE7 ANNUAL_RATE_CHANGE8 ANNUAL_RATE_CHANGE9 ANNUAL_RATE_CHANGE10 ANNUAL_RATE_CHANGE11 ANNUAL_RATE_CHANGE12 ANNUAL_RATE_CHANGE13 ANNUAL_RATE_CHANGE14 ANNUAL_RATE_CHANGE15 ANNUAL_RATE_CHANGE16 ANNUAL_RATE_CHANGE17 Year ALABAMA Autauga . . . . . 2.7881 . . . -0.65762 3.3834 -0.93699 . . . . . . ALABAMA Autauga . . 4.47282 . 0.72339 . . . . . 0.5008 -0.72382 . 57.7998 . . . . ALABAMA Autauga . . . . . 5.5284 . . -0.6409 . . -0.90544 . 2.7048 . . . . ALABAMA Autauga . . . . 8.43179 . . . 0.3604 -0.87704 . . . 15.2396 . . . . ALABAMA Autauga . . . . . 34.7286 . . . -0.53423 -0.461 . . 4.5602 . . . . ALABAMA Autauga . . . . . . . . 1.3445 . 1.0417 -0.96604 . . . . . . ALABAMA Autauga . . . . . . . . . -0.19729 . -0.87136 . . . . . . ALABAMA Autauga . . . . . . . . 11.8308 . 2.5186 . . 0.9968 . . . . ALABAMA Autauga . . . . . . . . . 3.39306 . -0.77831 . 6.564 . . . . ALABAMA Autauga . . . . . . . . . . 18.2565 . . . . . . . Thank you for your help and I look forward to hearing from you! Please let me know if there is more I can provide to make my question more clear.
... View more
