BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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

10 REPLIES 10
FriedEgg
SAS Employee

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
Super User

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
Quartz | Level 8

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
Super User

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
SAS Employee

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
Quartz | Level 8

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
Opal | Level 21

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

podarum
Quartz | Level 8

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
Opal | Level 21

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
SAS Employee

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 8629 views
  • 0 likes
  • 5 in conversation