Longtitudal calculations - product, year, cost.

[RetiSas]

I have a database which contain years, car model and annual service cost. Each car had a service cost each year along with other data.

I need to find out how to calculate the increase of values for each model over the years.

The database looks like this (simplified), contains about 10000 observations:

Year	Type	Value	Others
2020	Toyota	$ 20000
2019	Toyota	$ 16000
2018	Toyota	$ 14000
2017	Toyota	$ 14000
2020	Audi	$ 20000
2019	Audi	$ 18000
2018	Audi	$ 17000
2017	Audi	$ 15000

The results should be look like this:

Type	Change in $ from 2017 to 2020	Average change per year in $

My apologies I am a beginner, unfortunately I am not even sure how to approach this problem.

Thank you for any help on this. I could not find anything similar to this problem here.

[Kurt_Bremser]

Sort by type and year.

In a data step with

by type;

retain a variable for first_value. Set this value at first.type or when year = 2017 is encountered. At last.type, or when year = 2020 is encountered, do the calculations and OUTPUT.

[Ksharp · Accepted solution]

data have;
infile cards expandtabs truncover;
input Year	Type	$  Value dollar32.;
cards;
2020	Toyota	$ 20000	 
2019	Toyota	$ 16000	 
2018	Toyota	$ 14000	 
2017	Toyota	$ 14000	 
2020	Audi	$ 20000	 
2019	Audi	$ 18000	 
2018	Audi	$ 17000	 
2017	Audi	$ 15000
;
data want;
 do i=1 by 1 until(last.type);
  set have;
  by type notsorted;
  if first.type then first=value;
 end;
  diff=first-value;
  mean=diff/i;
  keep type diff mean;
run;