topic calculate growth rate within groupings in SAS Procedures

calculate growth rate within groupings

podarum — Wed, 07 Dec 2011 21:21:17 GMT

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

Re: calculate growth rate within groupings

FriedEgg — Wed, 07 Dec 2011 22:14:08 GMT

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

calculate growth rate within groupings

Reeza — Wed, 07 Dec 2011 22:28:01 GMT

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;

calculate growth rate within groupings

Pritish — Wed, 07 Dec 2011 23:03:01 GMT

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.

Re: calculate growth rate within groupings

Reeza — Wed, 07 Dec 2011 23:08:50 GMT

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;

calculate growth rate within groupings

FriedEgg — Wed, 07 Dec 2011 23:43:55 GMT

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;

calculate growth rate within groupings

podarum — Thu, 08 Dec 2011 14:07:09 GMT

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;

calculate growth rate within groupings

art297 — Thu, 08 Dec 2011 14:12:07 GMT

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

calculate growth rate within groupings

podarum — Thu, 08 Dec 2011 14:18:19 GMT

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

calculate growth rate within groupings

art297 — Thu, 08 Dec 2011 14:31:20 GMT

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.

calculate growth rate within groupings

FriedEgg — Thu, 08 Dec 2011 16:35:53 GMT

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;