Calculate Annual Rate Changes, per year increase

[brianathrift]

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.

[PaigeMiller]

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).

For each year, as it stands, you have up to 16 different growth rates. (Most years will have fewer than 16, but more than 1). So, please show us the desired one column for rate of change that you would like. Where do the 16 different rates go to make this a single column?

Also, you may want to consider how to handle missing values such that when 2004 is not present for a county, it is still assumed to be there; your current code just assumes 2005 is one year after 2003, because there is no record for 2004.

[brianathrift]

Thank you Paige. I will need to consider the missing data, per county and per year.

And I am planning to have changes per year window (i.e.: 2001-2002, 2002-2003, 2003-2004). Is there a way to make this one column variable? Or will I have to have multiple columns for "Changes 2001-2002", "Changes 2002-2003", "Changes 2003-2004"...and so forth? 

[unison]

One quick note is that you have "bar" instead of "var".

Proc Transpose Data=Want out=Long prefix=Annual_rate_change ;
   	By State County;
	Var  growth1yr growth2yr growth3yr growth4yr growth5yr growth6yr growth7yr growth8yr
	growth9yr growth10yr growth11yr growth12yr growth13yr growth14yr growth15yr growth16yr;
run;

[unison]

Making adjustments to my response on your last post!

Data Ped_fatal;
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;

Proc Sort Data=Ped_fatal;
	By State County year;
Run;

proc transpose data=ped_fatal prefix=rate out=trx(drop=_name_);
	by state county;
	id year;
	var Ped_rates;
run;

proc sql noprint;
	select name into :all_yrs separated by " " from dictionary.columns where 
		libname='WORK' and memname='TRX' and name like "rate%" order by name;
	select count(distinct(name)) into :cnt_yrs from dictionary.columns where 
		libname='WORK' and memname='TRX' and name like "rate%" order by name;
quit;

data trx;
	retain state county &all_yrs.;
	set trx;
run;

data want(drop=i);
	set trx;
	array rate[&cnt_yrs.] rate:;

	array temp[&cnt_yrs.] _temporary_;
	do i=1 to dim(rate);
		if rate[i]=. then
			rate[i]=0;
		temp[i]=rate[i];
	end;
	do i=2 to dim(rate);
		rate[i]=temp[i]/temp[i-1]-1;
	end;
	rate[1]=0;
	format rate: COMMA.2;
run;

Finally you would merge these "rate" results with your original dataset if you wanted to show the Ped_rates in your output.

[PaigeMiller]

Yes, @unison, this is very good, the idea of transposing the original data and then you can use arrays rather than lag functions is something I thought of too. I'm still concerned about missing values here, and I'm not 100% sure I know what "single column" output is desired from the original question.

[brianathrift]

Hello,

Thank you all for your responses! I was hoping to have one single column of "annual rate changes", where the year prior would be the rate that the change was derived from. I was thinking this would allow me to see if the annual fatality rate increased, or decreased, per year, given the respective county of the fatalities. 

[PaigeMiller]

---

@brianathrift wrote:

Hello,

Thank you all for your responses! I was hoping to have one single column of "annual rate changes", where the year prior would be the rate that the change was derived from. I was thinking this would allow me to see if the annual fatality rate increased, or decreased, per year, given the respective county of the fatalities. 

---

SHOW US, please.

[brianathrift]

Sorry, this is an example of the output I am looking for. Where 'Changes in Rates' would be missing for the first year, since this is the first year and the baseline. Else, the 'changes in rates' would be missing if the county has not been mentioned yet, or if the county was not accounted for the year before. I would like the changes in rates to be from year-to-year, where the prior year is baseline. If the ped_rate the year prior is not available, then I will count the changes in rates as missing. 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.

Year	State	County	Ped_rate	Change in Rates
2001	ALABAMA	Baldwin	4.4554	.
2001	ALABAMA	Bullock	0.6903	.
2001	ALABAMA	Calhoun	10.3939	.
2001	ALABAMA	Clarke	4.7556	.
2001	ALABAMA	Coffee	1.9288	.
2001	ALABAMA	Conecuh	8.8044	.
2001	ALABAMA	Crenshaw	9.4612	.
2001	ALABAMA	Dallas	1.7975	.
2001	ALABAMA	Dekalb	2.7568	.
2001	ALABAMA	Elmore	8.2706	.
2001	ALABAMA	Henry	2.4877	.
2001	ALABAMA	Jackson	12.7575	.
2001	ALABAMA	Jefferson	43.4405	.
2001	ALABAMA	Lauderdale	13.988	.
2001	ALABAMA	Lee	35.1568	.
2001	ALABAMA	Lowndes	2.2875	.
2001	ALABAMA	Macon	1.8251	.
2001	ALABAMA	Madison	50.2152	.
2001	ALABAMA	Marshall	8.4402	.
2001	ALABAMA	Mobile	26.9767	.
2002	ALABAMA	Autauga	1.5129	.
2002	ALABAMA	Baldwin	41.6898	0.023986683
2002	ALABAMA	Cherokee	2.6072	.
2002	ALABAMA	Chilton	7.3703	.
2002	ALABAMA	Dallas	0.9538	1.048437828
2002	ALABAMA	Dekalb	3.1252	0.319979521
2002	ALABAMA	Greene	0.228	.
2002	ALABAMA	Hale	3.7803	.
2002	ALABAMA	Jackson	11.3559	0.088059951
2002	ALABAMA	Jefferson	13.7646	0.072650131
2002	ALABAMA	Limestone	2.3518	.
2002	ALABAMA	Lowndes	2.1397	.
2002	ALABAMA	Madison	14.0052	0.071402051
2002	ALABAMA	Marshall	2.7964	0.357602632
2002	ALABAMA	Mobile	4.2689	0.234252384
2002	ALABAMA	Montgomery	6.4434	.
2002	ALABAMA	Pike	3.6759	.
2002	ALABAMA	Russell	31.0583	.
2002	ALABAMA	Shelby	1.5569	.
2002	ALABAMA	St. Clair	0.6259	.
2003	ALABAMA	Autauga	.	.
2003	ALABAMA	Baldwin	22.1484	0.045149988
2003	ALABAMA	Barbour	.	.
2003	ALABAMA	Blount	8.9222	.
2003	ALABAMA	Butler	.	.
2003	ALABAMA	Calhoun	6.6503	.
2003	ALABAMA	Chambers	5.6468	.
2003	ALABAMA	Chilton	.	.
2003	ALABAMA	Clarke	0.2592	.
2003	ALABAMA	Cleburne	12.486	.
2003	ALABAMA	Coffee	3.6551	.
2003	ALABAMA	Colbert	2.5004	.
2003	ALABAMA	Conecuh	.	.
2003	ALABAMA	Coosa	8.7085	.
2003	ALABAMA	Covington	3.1731	.
2003	ALABAMA	Crenshaw	.	.
2003	ALABAMA	Cullman	2.4942	.
2003	ALABAMA	Dale	9.6015	0.104150393
2003	ALABAMA	Dallas	2.1509	0.464921661
2003	ALABAMA	DeKalb	.	.

[PaigeMiller]

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? 

[mkeintz]

Your example data not only has missing ped_rates for some years, but in some cases also has missing annual records entirely (i.e. Autuga 2004 is not present at all).  This means that your lag values will not be consistent.  Sometimes LR4 will refer to 4 years ago, and sometimes it will refer to more than 4 years ago.  Is this intended?  Or do you want to make these variables consistent over your entire time range?

[brianathrift]

Hello!

Thank you for your response. 

This dataset is difficult, as I integrated several datasets across the counties and years. Therefore, there is data available for some years, and not others. I am considering running multiple imputations, or running a likewise deletion in order to have a more complete dataset where the dependent variable (pedestrian fatality rates) is available.

[mkeintz]

The issue, however, is whether you want LR4, etc. to have consistent meanings.

Let's assume you do, but let's assume also you don't want to put in synthetic records for the missing years.

Then I also agree that arrays are the way to go, but I think this can all be done in one data step, as long as the data are already sorted by state/county/year:

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 want (drop=_:);
  set ped_fatal;
  by state county;
  retain _begyear;   /* The earliest year encountered for current county*/

  array history {2001:2017} _temporary_;  /*Current county history*/

  array LR {16};    /* Assuming you want up to 16 lagged PED_RATES */
  array GRWTH {16}; /* And also 16 growth rates */

  if first.county then do;
    _begyear=year;
    call missing(of history{*});
  end;

  history{year}=ped_rates;

  do _Lag=1 to year-_begyear;
    LR{_lag}   = history{year-_LAG};
    if nmiss(ped_rates,LR{_lag})=0 then GRWTH{_lag}= (ped_rates-LR{_lag})/LR{_lag};
  end;
run;

1. Make sure the HISTORY array has array bounds that cover the entire date range of your data.
2.  Make sure that the LR and GRWTH arrays have enough elements to corresponding with your date range (i.e. 1 less than the number of years in your data set).

edited addition: I'm not sure this generates your "one column" request, but I would need example desired output to understand correctly.