Determine rates of change for different groups

Question

I have a SAS issue that I know is probably fairly straightforward for SAS users who are familiar with array programming, but I am new to this aspect.

My dataset looks like this:

Data have;          
Input group $ size price;
Datalines;
A 24 5
A 28 10
A 30 14
A 32 16
B 26 10
B 28 12
B 32 13
C 10 100
C 11 130
C 12 140
;
Run;

What I want to do is determine the rate at which price changes for the first two items in the family and apply that rate to every other member in the family.

So, I’ll end up with something that looks like this (for A only…):

Data want;         
Input group $ size price newprice;
Datalines;
A 24 5 5 
A 28 10 10
A 30 14 12.5
A 32 16 15
;
Run;

Answer 1

Here a different approach that is obviously longer than Joe's, but could be generalized to other similar situations where the calculation is different or depends on more values.

Add a sequence number to your data set:

data have2;
  set have;
  by group;
  if first.group the seq = 0;
  seq + 1;
run;

Use proc reg to calculate the intercept and slope for the first two rows of each group, outputting the estimates with outest:

proc reg data=have2 outest=est;
  by group;
  model price = size;
  where seq le 2;
run;

Join the original table to the parameter estimates and calculate the predicted values:

proc sql;
create table want as
select
  h.*,
  e.intercept + h.size * e.size as newprice
from
  have h
  left join est e
  on h.group = e.group
order by
  group,
  size
;
quit;

Answer 2

The technique you'll need to learn is either retain or diff/lag. Both methods would work here.

The following illustrates one way to solve this, but would need additional work by you to deal with things like size not changing (meaning a 0 denominator) and other potential exceptions.

Basically, we use retain to cause a value to persist across records, and use that in the calculations.

data want;
  set have;
  by group;
  retain lastprice rateprice lastsize;
  if first.group then do;
    counter=0;
    call missing(of lastprice rateprice lastsize); *clear these out;
  end;
  counter+1;                                       *Increment the counter;
  if counter=2 then do;
    rateprice=(price-lastprice)/(size-lastsize);   *Calculate the rate over 2;
  end;
  if counter le 2 then newprice=price;             *For the first two just move price into newprice;
  else if counter>2 then newprice=lastprice+(size-lastsize)*rateprice; *Else set it to the change;
  output;
  lastprice=newprice;        *save the price and size in the retained vars;
  lastsize=size;
run;