calculate growth rate within groupings

[podarum]

Hi, I'm trying to calcutae a growth/loss rate within various groupings.  I have ETS if that helps, I tried something like lnprice = log(price) and diffy = dif(lnprice), but not quite.

Have:

Cars   Year   Price   City

GM    2001   $3400    Tor

GM    2002   $4000    Tor

GM    2003   $4300    Tor

GM    2001   $2800    Mon

GM    2002   $2900    Mon

Ford   2001   $2200    Mon

Ford   2002   $2500    Mon

Want:

Cars   Year   Price   City       GrowthRate

GM    2001   $3400    Tor               -

GM    2002   $4000    Tor              17.6%

GM    2003   $4300    Tor              7.5%

GM    2001   $2800    Mon             -

GM    2002   $2900    Mon             3.5%

Ford   2001   $2200    Mon             -       

Ford   2002   $2000    Mon            -9.1%

Thanks

[FriedEgg]

data foo;

format price dollar.;

input cars $ year $ price city $;

cards;

GM 2001 3400 Tor

GM 2002 4000 Tor

GM 2003 4300 Tor

GM 2001 2800 Mon

GM 2002 2900 Mon

Ford 2001 2200 Mon

Ford 2002 2000 Mon

;

run;

proc sort data=foo; by city cars year; run;

data bar;

format growthrate percent8.2;

set foo;

by city cars year;

lag_price=ifn(first.cars,0,lag(price));

growthrate=(price/lag_price)-1;

drop lag_price;

run;

                                        Obs    growthrate    price     cars    year    city
                                         1         .         $2,200    Ford    2001    Mon 
                                         2      ( 9.09%)     $2,000    Ford    2002    Mon 
                                         3         .         $2,800    GM      2001    Mon 
                                         4        3.57%      $2,900    GM      2002    Mon 
                                         5         .         $3,400    GM      2001    Tor 
                                         6       17.65%      $4,000    GM      2002    Tor 
                                         7        7.50%      $4,300    GM      2003    Tor 

[Reeza]

Whats the ln/log there for in the above? Is that the name of your variables.

It looks like a % change calc, so new-previous/previous.

The Have data isn't consistent with the want data for the last point so not sure if something else is happening or not.

data have;

input cars $ year price dollar8. city $;

cards;

GM    2001   $3400    Tor

GM    2002   $4000    Tor

GM    2003   $4300    Tor

GM    2001   $2800    Mon

GM    2002   $2900    Mon

Ford   2001   $2200    Mon

Ford   2002   $2500    Mon

;

run;

proc sort data=have;

    by cars city year ;

run;

data want;

    set have;

     by cars city year;

     prev_price=lag(price);

    growth_rate=(price-prev_price)/prev_price;

    format growth_rate percent8.1;

    if first.city then growth_rate=.;

run;

[Pritish]

Reeza, I think Matt has the correct approach (you can also use conditional loop over here). Your code won't find whether it's the first observation in that group or not.

So, the below code will check whether the currect observation is the first in it group. If so, it will assign a value of 0 to the lag_price variable. If not, it will assign the previous value of price.

lag_price=ifn(first.cars,0,lag(price))

* Matt: I was not aware that SAS has IFC and IFN function. I was trying to use the if then else condition to solve this problem.

[Reeza]

I don't like the division by zero warnings in my log, so if its the first in a group I set it to missing manually using the last line in my code above (before the run).

I do have the sort order wrong though.

making the modifications as below and you don't get the message in your log. IfN obviously works as well.

data have;

input cars $ year price dollar8. city $;

cards;

GM    2001   $3400    Tor

GM    2002   $4000    Tor

GM    2003   $4300    Tor

GM    2001   $2800    Mon

GM    2002   $2900    Mon

Ford   2001   $2200    Mon

Ford   2002   $2500    Mon

;

run;

proc sort data=have;

    by city cars year ;

run;

data want;

    set have;

     by city cars year;

     prev_price=lag(price);

    growth_rate=(price-prev_price)/prev_price;

    format growth_rate percent8.1;

    if first.cars then growth_rate=.;

run;

[FriedEgg]

data foo;

format price dollar.;

input cars $ year $ price city $;

cards;

GM 2001 3400 Tor

GM 2002 4000 Tor

GM 2003 4300 Tor

GM 2001 2800 Mon

GM 2002 2900 Mon

Ford 2001 2200 Mon

Ford 2002 2000 Mon

;

run;

proc sort data=foo; by city cars year; run;

proc fcmp outlib=work.func.math;

function grate(num,den);

  if den=0 then return(.);

  else do;

   result=(num/den)-1;

   return(result);

  end;

endsub;

run;

options cmplib=(work.func);

data bar;

format growthrate percent8.2;

set foo;

by city cars year;

lag_price=ifn(first.cars,0,lag(price));

growthrate=grate(price,lag_price);

drop lag_price;

run;

[podarum]

Thanks FriedEgg,

What if I wanted to add another variable such as (below).  And I wanted to get the growth rate by Cars, Year, City and SubCity ??

data foo;

format price dollar.;

input cars $ year $ price city $ subcity $;

cards;

GM 2001 3400 Tor  Sco

GM 2002 4000 Tor  Sco

GM 2003 4300 Tor  NY

GM 2004 4500  Tor NY

GM 2001 2800 Mon  Gro

GM 2002 2900 Mon  Gro

Ford 2001 2200 Mon  Bal

Ford 2002 2000 Mon  Bal

;

run;

[art297]

Couldn't you just include that variable list in the by statements for BOTH your sort and datastep?

[podarum]

When I did that, the growth rate gets calculated through the SubCity as well..

If I do Reeza's code with the additional if first.SubCity then growth_rate = .;  then that fixes it all..

Thanks

[art297]

Glad to hear that you have a solution, but FE's solution should work, as well, as long as your by variable list is correct.  His code appears to simply want the last two variables to be cars year.  They can be preceded by just city, or city subcity.

Of course, if you change the order of the variables, you have to change the if statement to work correctly with either solution.

[FriedEgg]

proc sort data=foo; by city subcity cars year; run;

proc fcmp outlib=work.func.math;

function grate(num,den);

  if den=0 then return(.);

  else do;

   result=(num/den)-1;

   return(result);

  end;

endsub;

run;

options cmplib=(work.func);

data bar;

format growthrate percent8.2;

set foo;

by city subcity cars year;

lag_price=ifn(first.cars,0,lag(price));

growthrate=grate(price,lag_price);

drop lag_price;

run;